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=...
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

     

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

 

  • Silutions's avatar
    Silutions
    ETL
    04-01-2022

    Hari - Exactly what I needed.  Thank you. - Jim

  • 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")
    })