Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
Thanks for your reply but i don't know where on the UI i can do this.
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.
Thank you very much Nicole, it works perfectly!
I'm glad it works for you