Switch table structure

[#1]

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

[#2]

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

[#3]

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

[Nicole-Smith · Accepted solution]

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.

[#5]

Thank you very much Nicole, it works perfectly!

[Nicole-Smith]

I'm glad it works for you