Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am creating pivot table in qliksense. Below is my sample data:
RACCT | Segment | Amount |
1000000 | Lead | 1 |
1000010 | Buildestate | 1 |
1001000 | Aluminium | 1 |
1001010 | Lead | 1 |
1002000 | Aluminium | 1 |
1003000 | Lead | 1 |
1004000 | Aluminium | 1 |
1005000 | Aluminium | 1 |
I have attached the screenshot of current output.
But I require below-mentioned output:
RACCT | Segment | Aluminium | Buildestate | Lead |
1000000 | 0 | 0 | 1 | |
1000010 | 0 | 1 | 0 | |
1001000 | 1 | 1 | 0 | |
1001010 | 0 | 0 | 1 | |
1002000 | 1 | 1 | 0 | |
1003000 | 0 | 0 | 1 | |
1004000 | 1 | 1 | 0 | |
1005000 | 1 | 1 | 0 |
I want 0 at the place of missing values in table.
I have tried isnull, numsum, rangesum, len and alt functions.
Kindly suggest.
My suggestions:
Hi,
may be
Generic
LOAD RACCT,
Segment,
Amount
FROM
https://community.qlik.com/message/1358479
(html, codepage is 1252, embedded labels, table is @1);
Regards,
Antonio
Missing values are never calculated, so you cannot handle them by some or other expression.
In QV, I would change the missing value symbol to '0'. I am not sure if there is any way to handle them in QS.
Hello,
the problem is there do not exist rows in your table for all possible combinations which are visualized in pivot table so the pivot table does not know how to handle it. But you can generate the "full" table in the script (all possible combinations of RACCT and Segment) and then in your pivot table should be the measure Sum(Amount) = 0 in current missing places.
NoConcatenate
[table_new]:
LOAD Distinct
RACCT
Resident table;
join (table_new) LOAD Distinct
Segment
Resident table;
Left join [table_new]:
LOAD
RACCT,
Segment,
Amount
Resident table;
Drop table table;
BR,
Maria