Forum Discussion

zach_myt's avatar
zach_myt
Data Pipeline
05-30-2023
Solved

nulls to 0 in pivot

I am trying to get nulls in a pivot to show as 0's, which seems like it should be very basic built in functionality. I have tried If's, case's, and jquery scripts and nothing seems to work. I can do this at the SQL level buy I am trying to count in the widget and not in the query. 

I have read threads for 9 years ago where this was still a problem. How is this still an issue and not a quick function or easy setting???

What I've tried:

if(isnull(DUPCOUNT([device])),0, DUPCOUNT([device])) <-- this counts everything fine but blanks instead of 0

case when isnull(DUPCOUNT([device])) then 0 else DUPCOUNT([device]) end <-- this counts everything fine but blanks instead of 0

In the edit script section:

widget.transformPivot(
{
     type: ['member']
},
function (metadata, cell) {

if(cell.value === '') {
      cell.content = '0';
    }
  }
);

AND 

 
widget.on('ready', function(){
 
$('td[class*=p-value] div').map(function(i, cell) {
      if(cell.innerHTML == "&nbsp;" ) cell.innerHTML='0';
      })
    })
})

Any ideas?

Information Technology Analytics
  • Sijo1995's avatar
    Sijo1995
    06-01-2023

    Hi zach_myt ,

    This script for Linux region

    widget.transformPivot(
    {
        type: ['value']
    },
    function setCellBackground(metadata, cell) {
        if(cell.content.trim() == '')
			cell.content = '0'
    }
)

     

    Thanks

    Sijo

2 Replies

Replies have been turned off for this discussion
  • Sijo1995's avatar
    Sijo1995
    Cloud Apps
    06-01-2023

    Hi zach_myt ,

    This script for Linux region

    widget.transformPivot(
    {
        type: ['value']
    },
    function setCellBackground(metadata, cell) {
        if(cell.content.trim() == '')
			cell.content = '0'
    }
)

     

    Thanks

    Sijo