topic Re: Handel missing values in qliksense in App Development

Handel missing values in qliksense

rkspareek1992 — Mon, 09 Oct 2017 12:28:49 GMT

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.

Re: Handel missing values in qliksense

Anonymous — Tue, 10 Oct 2017 07:16:02 GMT

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

Re: Handel missing values in qliksense

antoniotiman — Tue, 10 Oct 2017 08:01:25 GMT

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

Re: Handel missing values in qliksense

jonathandienst — Thu, 12 Oct 2017 09:28:37 GMT

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.

Re: Handel missing values in qliksense

JaMajka1 — Thu, 12 Oct 2017 22:09:23 GMT

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