Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

rkspareek1992
Contributor

Handel missing values in qliksense

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 isnullnumsum, rangesum, len and alt functions.

Kindly suggest.

4 Replies
akarolewski
New Contributor III

Re: Handel missing values in qliksense

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
Honored Contributor III

Re: Handel missing values in qliksense

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

MVP
MVP

Re: Handel missing values in qliksense

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
Contributor

Re: Handel missing values in qliksense

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

Community Browser