topic Re: How to show dimension values as columns in pivot table in QlikView

How to show dimension values as columns in pivot table

trdandamudi — Wed, 25 Nov 2020 16:16:04 GMT

I have the following inline table:

Load * Inline [

Id,Batch,Product,Tree

1,A Batch,Apples,Level 1

2,B Batch,Cookies,Level 2

3,C Batch,Cake,Level 3

4,A Batch,Apples,Level 1

5,D Batch,Cookies,Level 3

6,E Batch,Apples,Level 1

7,F Batch,Cookies,Level 2

8,G Batch,Cake,Level 3

9,H Batch,Apples,Level 1

10,I Batch,Cookies,Level 3

];

I want the output in the bellow format:

By using Generic load will give me the above output ? I appreciate your time...

Thanks

Re: How to show dimension values as columns in pivot table

clondono — Fri, 16 Feb 2018 01:48:03 GMT

Is this something that you may be able to accomplish with a crosstable?

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/ScriptPrefixes/crosstable.htm

Re: How to show dimension values as columns in pivot table

trdandamudi — Fri, 16 Feb 2018 01:52:42 GMT

Carlos,

No Cross table will not help.

Thanks

Re: How to show dimension values as columns in pivot table

ramasaisaksoft — Fri, 16 Feb 2018 07:37:42 GMT

Dimensions are Batch,Product tree

Expression as =count(distinct Id)

once you created like this just drag and drop (Select the filed name with your mouse so it shows in a Red color border and drag that filed into right hand side)your Product and tree dimensions into Columns .

Re: How to show dimension values as columns in pivot table

trdandamudi — Fri, 16 Feb 2018 09:48:34 GMT

‌Rama,

This will not give the output the way I want my final output.

Thanks.

Re: How to show dimension values as columns in pivot table

Peter_Cammaert — Fri, 16 Feb 2018 11:26:32 GMT

What you try to do is turn both product and tree into a single dimension, correct?

Re: How to show dimension values as columns in pivot table

trdandamudi — Fri, 16 Feb 2018 11:32:16 GMT

Peter,

I want to turn the values in Product and Tree dimension into columns as shown in my final output. is this possible ?

Thanks

Re: How to show dimension values as columns in pivot table

Peter_Cammaert — Fri, 16 Feb 2018 11:33:06 GMT

If so, then the easiest thing to do would be to split product and tree into separate rows, with an indicator that tells you which of the two dimensions is used in a particular row Like in:

SpecialTable:

NOCONCATENATE

LOAD Id, Batch, Product As Dimension, 'Product' as DimensionType

RESIDENT SourceTable;

CONCATENATE (SpecialTable)

LOAD Id, Batch, Tree As Dimension, 'Tree' as DimensionType

RESIDENT SourceTable;

You can use this table for just the pivot output, or for various other things by manipulating the DimensionType in Set Analysis.

Re: How to show dimension values as columns in pivot table

sunny_talwar — Fri, 16 Feb 2018 11:34:49 GMT

This could be one option

Re: How to show dimension values as columns in pivot table

dineshm030 — Fri, 16 Feb 2018 11:40:32 GMT

Hi,

Am I correct?? Do you want like this result???

Re: How to show dimension values as columns in pivot table

Peter_Cammaert — Fri, 16 Feb 2018 11:40:39 GMT

See Sunny's solution below.

Re: How to show dimension values as columns in pivot table

ahmar811 — Fri, 16 Feb 2018 11:41:15 GMT

Hi Thirumala,

Please find your desire output I also, attached qvw file for more details

Regards

Ahmar

Re: How to show dimension values as columns in pivot table

sunny_talwar — Fri, 16 Feb 2018 11:42:06 GMT

You might have posted the incorrect qvw file...

Re: How to show dimension values as columns in pivot table

ahmar811 — Fri, 16 Feb 2018 11:45:15 GMT

Thanks sunny,

That's my mistake......

Re: How to show dimension values as columns in pivot table

trdandamudi — Fri, 16 Feb 2018 11:49:20 GMT

Looks like I am almost there... I got the dimension values as columns but the numbers are not correct. Am I missing something here ?

Thanks

Data:

Load * Inline [

Id,Batch,Product,Tree

1,A Batch,Apples,Level 1

2,B Batch,Cookies,Level 2

3,C Batch,Cake,Level 3

4,A Batch,Apples,Level 1

5,D Batch,Cookies,Level 3

6,E Batch,Apples,Level 1

7,F Batch,Cookies,Level 2

8,G Batch,Cake,Level 3

9,H Batch,Apples,Level 1

10,I Batch,Cookies,Level 3

];

SpecialTable:

NOCONCATENATE

LOAD Id as pvt_Id, Batch as pvt_Batch, Product As Dimension, 'Product' as DimensionType

RESIDENT Data;

CONCATENATE (SpecialTable)

LOAD Id as pvt_Id, Batch as pvt_Batch, Tree As Dimension, 'Tree' as DimensionType

RESIDENT Data;

Re: How to show dimension values as columns in pivot table

trdandamudi — Fri, 16 Feb 2018 11:57:56 GMT

Thanks Sunny.. This looks as a perfect fit. Can I also apply Top 5 using rank function on the expression ?

Re: How to show dimension values as columns in pivot table

sunny_talwar — Fri, 16 Feb 2018 12:00:29 GMT

It should work... what would the expected output look like with top 5?

Re: How to show dimension values as columns in pivot table

trdandamudi — Fri, 16 Feb 2018 12:06:17 GMT

Let me try it out and see how it works. As always thank you for the help.

Re: How to show dimension values as columns in pivot table

trdandamudi — Fri, 16 Feb 2018 13:10:13 GMT

Sunny,

Is it possible to have count for 'Product' column and % for 'Tree' column ? ('Tree' is a character column here but it is a numeric field originally) Sorry I missed this point to ask.

Thanks

Re: How to show dimension values as columns in pivot table

sunny_talwar — Fri, 16 Feb 2018 13:15:35 GMT

Like this?