Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a source table in the below format
and i want to use cross table load to change it to the below format
what can i do to achieve the above? thanks
May be this
Table:
CrossTable(Date, Value, 2)
LOAD ProductA as Product,
Date as Category,
[43101.000000],
[43102.000000],
[43103.000000],
[43104.000000]
FROM
[..\..\..\Downloads\Book12.xlsx]
(ooxml, embedded labels, table is Sheet3, filters(
Transpose(),
Replace(1, top, StrCnd(null))
));
FinalTable:
LOAD DISTINCT Product,
Date(Num#(Date)) as Date
Resident Table;
FOR i = 1 to FieldValueCount('Category')
LET vCategory = FieldValue('Category', $(i));
If '$(vCategory)' <> 'Date' then
Left Join (FinalTable)
LOAD Product,
Date(Num#(Date)) as Date,
Num(Num#(Value)) as [$(vCategory)]
Resident Table
Where Category = '$(vCategory)';
ENDIF
NEXT
DROP Table Table;
Ronald,
You could load the first 3 columns using the crosstable load and make a field called product with value of 'A'. Then concatenate load columns 4 to 6 using the same technique, but with the value 'B' for the product field.
I hope that helps.
May be this
Table:
CrossTable(Date, Value, 2)
LOAD ProductA as Product,
Date as Category,
[43101.000000],
[43102.000000],
[43103.000000],
[43104.000000]
FROM
[..\..\..\Downloads\Book12.xlsx]
(ooxml, embedded labels, table is Sheet3, filters(
Transpose(),
Replace(1, top, StrCnd(null))
));
FinalTable:
LOAD DISTINCT Product,
Date(Num#(Date)) as Date
Resident Table;
FOR i = 1 to FieldValueCount('Category')
LET vCategory = FieldValue('Category', $(i));
If '$(vCategory)' <> 'Date' then
Left Join (FinalTable)
LOAD Product,
Date(Num#(Date)) as Date,
Num(Num#(Value)) as [$(vCategory)]
Resident Table
Where Category = '$(vCategory)';
ENDIF
NEXT
DROP Table Table;
thanks, it is nice
hi i have a data in mysql i want to use cross table to load data . but i didn't found any solution.if you have a solution so kindly reply .