topic Re: Crossing the data in QlikView

Crossing the data

Tue, 05 Nov 2013 17:13:10 GMT

Hi All,

i have one table contains structure as follows

but my requirement is as follows

i'm attaching Excel files which contains data in Sheet1 and Sheet2.

Please somebody give me reply.

Thanks,

M V

Re: Crossing the data

Gysbert_Wassenaar — Tue, 05 Nov 2013 17:36:19 GMT

1. Load the data as it is

2. Create a pivot table with Pnum, Pname and Month as dimensions and sum(Actual) as expression

3. Drag the Month dimension to the right to above the expression. Let go when you see a horizontal blue line.

You should now have a pivot table that resembles your requirement.

You'll need a small inline table to sort the months correctly too. See attached example.

Re: Crossing the data

MayilVahanan — Tue, 05 Nov 2013 17:36:41 GMT

Use Pivot table and drag and drop . PFA

Re: Crossing the data

Tue, 05 Nov 2013 17:42:00 GMT

I did this example, I hope you serve

Re: Crossing the data

Clever_Anjos — Tue, 05 Nov 2013 17:51:30 GMT

If you want to do this at script level you could try something like this

Generic
LOAD Pnum, 
     Pname, 
     Month, 
     Actual
FROM [Book1.xlsx](ooxml, embedded labels, table is Sheet1);


Load Distinct Month 
FROM [Book1.xlsx] (ooxml, embedded labels, table is Sheet1);


set v='Noconcatenate Final:';
for t = 1 to FieldValueCount('Month') 

    let tab ='Sheet1.'&FieldValue('Month',$(t));
    $(v)
    LOAD *
    Resident $(tab);
    set v='join(Final)';
    drop Table $(tab);
next