Switch table structure

Mon, 27 Jan 2014 12:28:27 GMT

Hello,

I have a table like this one:

And i want to get a table like this:

I want to get a column for each trio Division/Magasin/Hiérarchie produit and only 1 line per day.

How can i do this? Maybe with CrossTable?

Thanks,

Best Regards,

Loïc

Re: Switch table structure

Mon, 27 Jan 2014 13:53:34 GMT

Hi , You get this with Pivot table on the UI. If you want this in the script level, you need to use generic load or For loop

Re: Switch table structure

Mon, 27 Jan 2014 14:19:10 GMT

Thanks for your reply but i don't know where on the UI i can do this.

Re: Switch table structure

Nicole-Smith — Mon, 27 Jan 2014 14:26:51 GMT

Load script like the following should work:

DataTemp:

LOAD Date, text(Division)&'/'&text(Magasin)&'/'&[Hiérarchie produit] as [Division/Magasin/Hiérarchie produit], [Stock cumulé] INLINE [

    Date, Division, Magasin, Hiérarchie produit, Stock cumulé

    24.01.2014, 0100, 0010, 8101, 0.02

    24.01.2014, 0100, 0901, 8101, 0.388

    24.01.2014, 0100, 0001, 8102, 201.903

    24.01.2014, 0100, 0010, 8102, -101.533

    24.01.2014, 0100, 0901, 8102, 3.804

    24.01.2014, 0100, 0001, 8115, 0.663

    24.01.2014, 0100, 0001, 8117, 252.009

    24.01.2014, 0100, 0010, 8117, -115.254

    24.01.2014, 0100, 0901, 8117, 1.215

    24.01.2014, 0100, 0001, 8119, 19.309

    24.01.2014, 0100, 0010, 8119, -2.827

    24.01.2014, 0100, 0901, 8119, 0.749

    24.01.2014, 0100, 0001, 8133, 134.642

    24.01.2014, 0100, 0010, 8133, -2.084

    24.01.2014, 0100, 0901, 8133, 0.663

    24.01.2014, 0100, 0001, 8155, 97.48

    24.01.2014, 0100, 0010, 8155, 1.03

];

Let vNumOfValues = FieldValueCount('Division/Magasin/Hiérarchie produit');

Set vJoin = ;

FOR i = 1 TO $(vNumOfValues)

    LET vFieldName = '[' & PEEK('Division/Magasin/Hiérarchie produit', $(i)-1, 'DataTemp') & ']';

    LET vFieldValue = chr(39) & PEEK('Division/Magasin/Hiérarchie produit', $(i)-1, 'DataTemp') & chr(39);



    Data:

    $(vJoin)

    LOAD Date,

        [Stock cumulé] as $(vFieldName)

    RESIDENT DataTemp

    WHERE [Division/Magasin/Hiérarchie produit] = $(vFieldValue);



    SET vJoin = OUTER JOIN;



NEXT

DROP TABLE DataTemp;

Example file also attached.

Re: Switch table structure

Mon, 27 Jan 2014 14:39:57 GMT

Thank you very much Nicole, it works perfectly!

Re: Switch table structure

Nicole-Smith — Mon, 27 Jan 2014 14:40:22 GMT

I'm glad it works for you

topic Switch table structure in QlikView

Switch table structure

Re: Switch table structure

Re: Switch table structure

Re: Switch table structure

Re: Switch table structure

Re: Switch table structure