topic Re: How to Show all value but Null in Pivot Table in QlikView

How to Show all value but Null in Pivot Table

Anonymous — Tue, 15 Aug 2017 09:11:20 GMT

Dear All

I have a problem about report interface.

In my report I need to show EffectiveMonth with 24row and Monthdifferenct with 25 column.

There is no problem if I won't drill down to any selection.

But when I use the selection.My report show Null dimension in the last column like this.

The dimension name "No" is from Inline Load so I don't understand Where "Null" column came from. and How could I avoid it without skip some of EffectiveMonthYear and No 1-25

Please kindly advise.

Thank you

Re: How to Show all value but Null in Pivot Table

sunny_talwar — Tue, 15 Aug 2017 11:18:15 GMT

Would you be able to share a sample to take a look at this?

Re: How to Show all value but Null in Pivot Table

Anonymous — Wed, 16 Aug 2017 02:14:06 GMT

Hi Sunny

Thank you for your kind attention.

For data security I have to edit some sensitive data,Please give me some time.

Re: How to Show all value but Null in Pivot Table

qliksus — Wed, 16 Aug 2017 05:57:28 GMT

Have you modified your null value as "Missing" in the presentation of the chart like this and the reason could be that those months might not have any data for the selection you made

Re: How to Show all value but Null in Pivot Table

Anonymous — Wed, 16 Aug 2017 06:31:22 GMT

Dear Susant

Yes,I have. I don't have any concern about any column but "Null" column on the right handside.
I would like to remove this excess column with out skip or surpress column "No" 1-25 and row "EffMonth".

Thank you

Re: How to Show all value but Null in Pivot Table

qliksus — Wed, 16 Aug 2017 09:36:19 GMT

The reason is there is some month which doesn't have the required value in field "No" in your data . Consider the below example if you connect the data through "No" the april month don't have the data so when you take this in chart you will get the null value

LOAD * INLINE [
    MonthYear, No
    Jan,1
    Feb,2
    Mar,3
    Apr,-
];

LOAD * INLINE [
   No
   1
   2
   3
    ] ;

You can filter the null values using expression like something below to get the result

If ( Isnull(No) ,0,sum(amount)

Re: How to Show all value but Null in Pivot Table

sunny_talwar — Wed, 16 Aug 2017 11:31:38 GMT

Sure thing... may be this thread can help you fasten things up

Preparing examples for Upload - Reduction and Data Scrambling

Re: How to Show all value but Null in Pivot Table

Anonymous — Thu, 17 Aug 2017 06:38:16 GMT

Dear Sunny

I already uploaded file.

Thank you for your very kind.

Re: How to Show all value but Null in Pivot Table

Anonymous — Thu, 17 Aug 2017 06:43:59 GMT

Dear Susan

I have tried this solution you adviced but the output was the same.

Please kindly see my attached I'm not sure if I miss something with the expression.

Thank you so much.

Re: How to Show all value but Null in Pivot Table

tresB — Thu, 17 Aug 2017 06:50:22 GMT

Yours seems to be a cross-table incomplete Cartesian product generated nulls/missing (you marked as 'Missing' in the chart), and that is why you don't see it/gets generated in the script. Please refer below the snippet from HIC's document:

Re: How to Show all value but Null in Pivot Table

Anonymous — Thu, 17 Aug 2017 07:12:02 GMT

Hi Tresesco,

My bad,May be my content subject is not clear (Sorry for my English).

I need to show all dimension and lock it to show 24rows and 25column or 24rows and 13column (Please see my attached) no matter there are transaction or not (If there are no tranasction show 0)

I would like my table to show every "EffMonthYear" and "No".

To achive this purpose I use fuction "Show all values" in Chart Properties.

My problem is,It show the excess column at the right side of the table

There is anyway I could terminate this excess column.

Thank you

Re: How to Show all value but Null in Pivot Table

Anonymous — Thu, 17 Aug 2017 07:35:07 GMT

Dear All

How about I fix this by using 25 expression instead?

Re: How to Show all value but Null in Pivot Table

tresB — Thu, 17 Aug 2017 07:46:33 GMT

Would not be a nice solution. However, you are already using a not-so-dynamic expression (using 25 IFs for 25 dimension values); so if that works, you might give a try.