topic Re: Pivot table not showing all fields in Dimension in QlikView

Pivot table not showing all fields in Dimension

Wed, 04 May 2016 14:23:23 GMT

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

Re: Pivot table not showing all fields in Dimension

krishnacbe — Wed, 04 May 2016 16:17:10 GMT

Hi,

Goto Presentation tab and uncheck the Suppress Zero-Values and Suppress Missing check boxes.

Re: Pivot table not showing all fields in Dimension

Anonymous — Wed, 04 May 2016 16:21:44 GMT

.....and maybe as well tick Show All Values for the relevant dimensions.

Re: Pivot table not showing all fields in Dimension

sunny_talwar — Wed, 04 May 2016 16:48:19 GMT

Are you using Class function to create the buckets? Class function won't create a class if there is no data to display within the specific bucket. You might want to consider using if statement or some other approach. In the below link marcowedel‌ used ValueLoop.

Displaying empty buckets in class function

Re: Pivot table not showing all fields in Dimension

Thu, 05 May 2016 08:36:10 GMT

Thank you Krishnapriya, but it doesnt work.

Re: Pivot table not showing all fields in Dimension

Thu, 05 May 2016 08:37:56 GMT

Thank you Bill, but it doesnt work.

Re: Pivot table not showing all fields in Dimension

Thu, 05 May 2016 10:31:56 GMT

Thank you Sunny for the reply.

The Age bucket column is currently a column (already defined) in the raw data, based on the Age column.

I have tried the method suggested in the link above and researched Class and Value Loop, however it does not work.

We currently have the following:

Dimensions:

Failed Matched

Failed Reason

Failed Age Bucket

Expression:

No. of items = Count({<[Failed >500_K]={'>500 K'},[Failed Matched]={'Matched'}>}[Failed COB_GBP_Value])

Value (GBP) = Count({<[Failed >500_K]={'>500 K'},[Failed Matched]={'Matched'}>}[Failed COB_GBP_Value])

TM (GBP) = sum({<[Failed >500_K]={'>500 K'},[Failed Matched]={'Matched'}>}[Failed MTM_GBP])

We are missing one bucket ">30".

How would we create an IF statement to show the missing bucket?

Thanks again

Re: Pivot table not showing all fields in Dimension

sunny_talwar — Thu, 05 May 2016 16:25:05 GMT

Are you creating Bucket in the script or is it a calculated dimension? Can you share the expression or script used to create bucket?

Re: Pivot table not showing all fields in Dimension

Fri, 06 May 2016 07:57:50 GMT

No, I am not creating the Bucket in the script and it is not a calculated dimension.

The Age bucket is already calculated in the raw data and we are reading it into QlikView. The Age bucket is based on the Days Failed.

I am using the script below:

[Failed Report]:
LOAD
filename() as [Failed File name],
mid(filename(),26,8) as Name,
mid(filename(),26,2)&'/'&mid(filename(),28,2)&'/'&mid(filename(),30,4) as File_Name_Date,
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],
FROM
[..\Fails\*.txt]
(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

Thanks Sunny

Re: Pivot table not showing all fields in Dimension

sunny_talwar — Fri, 06 May 2016 09:49:39 GMT

This is weird, is there a possibility that there is not data available and that is why it isn't getting displayed? Its difficult to say without playing around with the data a little bit. Would you be able to share your application or a sample?

Preparing examples for Upload - Reduction and Data Scrambling

Re: Pivot table not showing all fields in Dimension

Fri, 06 May 2016 12:55:49 GMT

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 .

Re: Pivot table not showing all fields in Dimension

smirkinaa — Fri, 06 May 2016 14:43:05 GMT

Hi. Dimentions have property "Show all values"

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

Re: Pivot table not showing all fields in Dimension

Fri, 06 May 2016 16:04:44 GMT

Thank you very much andrii

It is working, have a great weekend

Re: Pivot table not showing all fields in Dimension

Mon, 09 May 2016 15:01:39 GMT

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

Re: Pivot table not showing all fields in Dimension

sunny_talwar — Mon, 09 May 2016 15:07:57 GMT

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

Re: Pivot table not showing all fields in Dimension

Mon, 09 May 2016 15:17:39 GMT

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