Pivot table not showing all fields in Dimension

[#1]

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

[#2]

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 · Accepted solution]

Hi. Dimentions have property "Show all values"

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

[#4]

Thank you very much andrii

It is working, have a great weekend

[#5]

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 days	4-9 days	10-30 days	>30 days
Matched	MATCHED - CPTY SHORT	No. of items	11	1	0	0
		Value (GBP)	14558060	903554	0	0
		MTM (GBP)	-162806	26462	0	0
	MATCHED - RBC SHORT	No. of items	19	0	0	0
		Value (GBP)	23672784	0	0	0
		MTM (GBP)	-67807	0	0	0
	MATCHED RECYCLED	No. of items	1	0	0	0
		Value (GBP)	899483	0	0	0
		MTM (GBP)	-1971	0	0	0
Total		No. of items	31	1	0	0
		Value (GBP)	39130327	903554	0	0
		MTM (GBP)	-232584	26462	0	0

• 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

[#7]

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