How to show dimension values as columns in pivot table

[trdandamudi]

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

[Peter_Cammaert]

See Sunny's solution below.

[ahmar811]

Hi Thirumala,

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

Regards

Ahmar

[sunny_talwar]

You might have posted the incorrect qvw file...

[ahmar811]

Thanks sunny,

That's my mistake......

[trdandamudi]

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;

[trdandamudi]

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

[sunny_talwar]

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

[trdandamudi]

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

[trdandamudi]

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

[sunny_talwar]

Like this?