Skip to main content
Announcements
Applications are open for the 2024 Qlik Luminary Program. Apply by December 15 here.
cancel
Showing results for 
Search instead for 
Did you mean: 
punitpopli
Specialist
Specialist

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

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

Labels (2)
3 Replies
Chirantha
Support
Support

Hello Punit,

 

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

Or
MVP
MVP

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
Partner - Creator III
Partner - Creator III

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;
 
cristianj23a_0-1692049350386.png

 

Regarts.

https://www.linkedin.com/in/cristianjorge/
Do not forget to mark as "Accepted Solution" the comment that resolves the doubt.