Skip to main content
Announcements
Do More with Qlik - Qlik Cloud Analytics Recap and Getting Started, June 19: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Pivot table not showing all fields in Dimension

Hi all,

Please can you assist, I am trying to replicate a pivot table in QlikView.

This table has 2 age buckets 0-3 days and 4-9 days, however when creating different views of the table, some of the age buckets are not showing, only the ones that has values.

How do I show everything regardless if the expressions are null.

I have tried following solutions posted on other forums, howeber it does not work.

Thank you very much.

Mary

15 Replies
Not applicable
Author

Yes, there is no data available.

lease find attached a sample of my application.

Thank you very much for your assistance with this issue Sunny .

smirkinaa
Contributor III
Contributor III

Hi. Dimentions have property "Show all values"

if we check it then we obtain all values of dimension in the chart

Not applicable
Author

Thank you very much andrii

It is working, have a great weekend

Not applicable
Author

Hi Andrii,

Thank you very much for your solution, the columns appear.

However I am getting new blank columns, which are hard to hide, I have tried resolving this (using the solutions mentioned below) and recommendations on other forums (e.g.

How to REMOVE "EMPTY RECODR in  a .txt file WHI... | Qlik Community,

, Show empty values in a pivot table | Qlik Community ) however had no luck .

End result, want to show this:

   

Matched Reason Bucket 0-3 days4-9 days10-30 days>30 days
MatchedMATCHED - CPTY SHORTNo. of items11100
Value (GBP)1455806090355400
MTM (GBP)-1628062646200
MATCHED - RBC SHORTNo. of items19000
Value (GBP)23672784000
MTM (GBP)-67807000
MATCHED RECYCLEDNo. of items1000
Value (GBP)899483000
MTM (GBP)-1971000
Total No. of items 31100
Value (GBP) 3913032790355400
MTM (GBP) -2325842646200

  • Query1 : Two new blank columns appear. See screenshot:

    

Attempt :  I check the "Suppress When Value is Null" and it dissapears. I have also modified the script to the below:

"[Failed Report]:
LOAD
DATE(Date#(mid(filename(),26,2)&'/'&mid(filename(),28,2)&'/'&mid(filename(),30,4),'DD/MM/YYYY'),'YYYY/MM/DD') as TD,
[Days Failed] as  [Failed Days Failed],
[COB GBP Value] as  [Failed COB_GBP_Value],
[>100 K] as  [Failed >100_K],
[>500 K] as  [Failed >500_K],
[Age Bucket] as  [Failed Age_Bucket],
[MTM GBP] as  [Failed MTM_GBP],
[Matched] as  [Failed Matched],
Reason as [Failed Reason]

FROM
[Fails Sample\*.txt]
(
txt, codepage is 1252, embedded labels, delimiter is '\t', msq)
where [Age Bucket]='0-3 days'
or [Age Bucket]='4-9 days'
or [Age Bucket]='10-30 days'
or [Age Bucket]='>30 days'; "


  • Query 2 :Blank columns above in the "Matched" column.


Attempt: I have tried replicating the above for the Dimension = "Matched", by selecting "Suppress When Value is Null" and the other columns disaapear.

Also tried modifying the edit script with the below:

FROM
[Fails Sample\*.txt]
(
txt, codepage is 1252, embedded labels, delimiter is '\t', msq)
where [Age Bucket]='0-3 days'
or [Age Bucket]='4-9 days'
or [Age Bucket]='10-30 days'
or [Age Bucket]='>30 days'
and Matched = 'Matched' or 'Unmatched'
;

and that doesnt work either

Thank you very much,

Mary

sunny_talwar

Right now there is not bucket >30 created in the application. If there is one, it would def. show up

Capture.PNG

Not applicable
Author

Thanks Sunny for the prompt reply.

Do you know can I can hide the blank unmatched?

Ideally we want the two tables in the attached to show the same Buckets?

Thanks again,

Mary