Forum Discussion

Silutions's avatar
Silutions
ETL
03-31-2022
Solved

Pivot2: Replace a value in a specific value in another column

Have this script to find and replace a specific cell value with another value:

function getWidgetDocumentObject(widget) {
if (prism.activeWidget == null)
return document.querySelector('[widgetid="' + widget.oid + '"]').querySelector('pivot2');
else
return document.querySelector('pivot2');
}

widget.on('ready', function(widget) {
docA = getWidgetDocumentObject(widget);
// Replace cell values
docA.querySelectorAll('.table-grid__cell').forEach((element) => {
if(element.querySelector('.table-grid__content').innerText == 'Spring Breeze') {
element.querySelector('.table-grid__content').innerText = 'Fall Leaves'
}
});
});

I would like to adapt this to search and replace values in a specific column.  Any help is greatly appreciated.

Regards, Jim

dashboards & reporting
  • harikm007's avatar
    harikm007
    04-01-2022

    Silutions ,

    Below script will replace cell values. Update the variable columnIndex with index of column you need.

     

    widget.transformPivot(
    {
        type: ['member']
    },
    function setCellBackground(metadata, cell) {
        
		columnIndex = 2
		
		if(metadata.colIndex == columnIndex)
		{
			cell.style = cell.style || {};
		
			if(cell.content == 'Cannon')
				cell.content = 'Cannon - Updated Text'
			else if(cell.content == 'Hickman')
				cell.content = 'Hickman - Updated Text'
				
		}

    }
);

     

    Note : This script wont replace numbers from Values panel

    -Hari

     

3 Replies

Replies have been turned off for this discussion
  • harikm007's avatar
    harikm007
    Data Warehouse
    04-01-2022

    Silutions ,

    Below script will replace cell values. Update the variable columnIndex with index of column you need.

     

    widget.transformPivot(
    {
        type: ['member']
    },
    function setCellBackground(metadata, cell) {
        
		columnIndex = 2
		
		if(metadata.colIndex == columnIndex)
		{
			cell.style = cell.style || {};
		
			if(cell.content == 'Cannon')
				cell.content = 'Cannon - Updated Text'
			else if(cell.content == 'Hickman')
				cell.content = 'Hickman - Updated Text'
				
		}

    }
);

     

    Note : This script wont replace numbers from Values panel

    -Hari

     

    • rvickersevotix's avatar
      rvickersevotix
      Cloud Apps
      12-15-2022

      Will this work on turning a number value into text?

      I have tried all of these options separetely to no avail.

       

      newItemMapping = {'2374421':'N/A', 
                  '2406788':'Production', 
                  '2406789':'Warehouse',
                  '2406790':'Garage', 
                  '2406787':'Office',
                  '2406791':'Transport', 
                  '2406792':'Yard'
                }
widget.on("queryend", function(se, ev){
    var panelName = 'Area' //Items in this panel will be replaced with new items
    panelIndex = ev.rawResult.headers.indexOf(panelName)
    $.each(ev.rawResult.values, function(index, value){
        if(newItemMapping[value[panelIndex].text] != undefined)
        {
            value[panelIndex].data = newItemMapping[value[panelIndex].data]
            value[panelIndex].text = newItemMapping[value[panelIndex].text]
        }
    })  
})


widget.on('ready', function(){
    $('td[class*=p-value] div').map(function(i, cell) { 
    switch (cell.innerHTML ) {
    case "2406788": cell.innerHTML='Production';
    break;
    case "2406789": cell.innerHTML='Warehouse';
    break;
    case "2406790": cell.innerHTML='Garage';
    break;
    case "2406791": cell.innerHTML='Transport';
    break;
    case "2406792": cell.innerHTML='Yard';
    break;
    default: cell.innerHTML='N/A';
    }
    }) 
    })
    
    
    
    
    
    widget.on('ready', () => {
    $('td.p-value[val="2406788"][fidx="6"]').text("Production")
    $('td.p-value[val="2406789"][fidx="6"]').text("Warehouse")
    $('td.p-value[val="2406790"][fidx="6"]').text("Garage")
    $('td.p-value[val="2406791"][fidx="6"]').text("Transport")
    $('td.p-value[val="2406792"][fidx="6"]').text("Yard")
    $('td.p-value[fidx=1] div').filter(function() {return parseFloat($(this).text().replace("$","").replace(",","")) = null;}).text("N/A")
    })