topic Handle missing value in Pivot Table in QlikView

Handle missing value in Pivot Table

girard_ben — Mon, 27 Jun 2016 16:01:22 GMT

Dear All,

In a POC application I have the tested the functionality of replaced missing value by a string.

cf my attached application...I have puted "Closed" in the pivot table when there is no row

This work well in my POC !

But when I do the same in my target application, it's not working...

so if someone has an idea ?

thanks in advance

Re: Handle missing value in Pivot Table

girard_ben — Mon, 27 Jun 2016 18:14:14 GMT

I add again my screenshot

Re: Handle missing value in Pivot Table

CarlosAMonroy — Mon, 27 Jun 2016 20:43:40 GMT

Hi,

You can use the following expression:

=if(isnull( sum(Traffic)) or sum(Traffic)= 0,'Closed',sum(Traffic))

And/or see attached images.

Thanks,

Carlos

Re: Handle missing value in Pivot Table

sunny_talwar — Tue, 28 Jun 2016 00:13:11 GMT

Is the attached application your target application? Can you may be elaborate on what you are looking for if what Carlos provided is not what you were looking for

Re: Handle missing value in Pivot Table

girard_ben — Tue, 28 Jun 2016 08:47:32 GMT

Hi Carlos,

thanks a lot for your quick reply and your help but unfortunately it's not working.

be careful : when we have 0 recored in the database, the store is opened...( we have some stores with no traffic..)

(each day... Store managers have to enter the traffic in the cash registers... )

So to detect if the store is opened is to check "the missing value"...

But test after tests, I think I will handle this problem in the load script.

I explain :

This "tips" seems to be working with a several days selected...

For exemple if I select one store and one day with not data recorded.. qlik view will hide the store.

but If I selected two days... the first with traffin and the second one with no traffic (no row for the given store in the database) this working..

I have one cell with an amount and the second one with "closed".

qlikview "auto hide" elements with no data.. I'll manage this in a script load, by generated aditionnal row for "closed" days..

thanks for your help.

Re: Handle missing value in Pivot Table

girard_ben — Mon, 04 Jul 2016 11:43:17 GMT

Dear all ,

I have found a quick solution : to count the number or rows if equal to 0 : the store is closed...

=if(count(Traffic)=0,'Closed', sum(Traffic))