cancel
Showing results for 
Search instead for 
Did you mean: 

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

Silutions
10 - ETL
10 - ETL

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

1 ACCEPTED SOLUTION

harikm007
13 - Data Warehouse
13 - Data Warehouse

@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

 

View solution in original post

3 REPLIES 3

harikm007
13 - Data Warehouse
13 - Data Warehouse

@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

 

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

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