Cross table load script

[ronaldwang]

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

[john_obrien]

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.

[sunny_talwar · Accepted solution]

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;

[ronaldwang]

thanks, it is nice

[kamikhan]

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 .