topic How to handle "null" in metric values in Pivot table in App Development

How to handle "null" in metric values in Pivot table

punitpopli — Mon, 14 Aug 2023 10:54:10 GMT

Hi All,

I wanted to know if their is way to handle null values in the metric column, sample data load script for reference -

Temp:
Load * Inline [
dim, Year,metric
a,2022,100
b,2022,110
c,2022,120
d,2022,130
e,2023,140
f,2022,150
g,2022,160
h,2022,170
i,2023,180
j,2022,190
k,2023,105
l,2023,109
];

Load data in a Pivot table

For e.g. a dim doesn't have any value for 2023 and would like to replace the same with 0 instead of "-"

Re: How to handle "null" in metric values in Pivot table

Chirantha — Mon, 14 Aug 2023 11:10:50 GMT

Hello Punit,

Please refer to this other post with a similar question and check if it helps

Re: How to handle "null" in metric values in Pivot table

Or — Mon, 14 Aug 2023 12:30:30 GMT

As far as I know, there is no way to change the null for a missing intersection in a pivot table. This has been discussed extensively on Community, so you should be able to find plenty of threads for it. You can either fill in the missing intersection in your data load, use a third-party option such as Vizlib Pivot (some of them have the option to set a custom null value which you can change to 0), or stick with the native offering and current data and get used to the null indicator being '-'.

Re: How to handle "null" in metric values in Pivot table

cristianj23a — Mon, 14 Aug 2023 21:42:48 GMT

Hello, use this code:

Temp:

Load * Inline [

dim, Year,metric

a,2022,100

b,2022,110

c,2022,120

d,2022,130

e,2023,140

f,2022,150

g,2022,160

h,2022,170

i,2023,180

j,2022,190

k,2023,105

,2023,109

,2023,125

n,2023,164

];

NoConcatenate

Table:

Load Year,

metric,

Pick(Match(dim,'',dim),0,dim) as dim

Resident Temp;

Drop Table Temp;

Regarts.