Handel missing values in qliksense

[rkspareek1992]

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.

[Anonymous]

My suggestions:

• this is the case of handling NULL values (missing values)
• unpivot vs pivot or cast vs melt (in R) - look for answers about those transforming techniques

[antoniotiman]

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

[jonathandienst]

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.

[JaMajka1]

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