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

[punitpopli]

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

[Chirantha]

Hello Punit,

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

[Or]

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

[cristianj23a]

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.