Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
trdandamudi
Master II
Master II

How to show dimension values as columns in pivot table

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:

Screenshot.jpg

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

Thanks

1 Solution

Accepted Solutions
sunny_talwar

This could be one option

Capture.PNG

View solution in original post

19 Replies
clondono
Creator III
Creator III

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...

trdandamudi
Master II
Master II
Author

Carlos,

No Cross table will not help.

Thanks

ramasaisaksoft

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 .

trdandamudi
Master II
Master II
Author

‌Rama,

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

Thanks.

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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

trdandamudi
Master II
Master II
Author

Peter,

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

Thanks

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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.

sunny_talwar

This could be one option

Capture.PNG

dineshm030
Creator III
Creator III

Hi,

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