Excel Data transformation

[arethaking]

Hi All,

I want to load the excel source data.

Note: I have many columns are there in the excel file with different Group text values.

Source Data Format:

Required Out put Format:

[olivierrobin]

hello

you can use file assistant

and you will obtain something like that

LOAD Country,

     Cust,

     Category,

     DimA,

     ProdA,

     Status,

     Value,

     Sales,

     Desc

FROM

[Data_Loading - Copy.xlsx]

(ooxml, embedded labels, header is 1 lines, table is DummyData);

[Neymar_Jr]

Hi

See if this helps - multi_header_pivot_import.qvw

Thanks,

RT

[bwisealiahmad · Accepted solution]

Hi,

You could looking into a For Each Loop for this and maybe map the GroupName in or else do something simpler like this.

Here is how I tried to solve it in the script:

Data:

LOAD Country,

     Cust,

     Category,

     DimA,

     ProdA,

     'Group1' AS GroupName

FROM

(ooxml, embedded labels, header is 1 lines, table is DummyData);

Concatenate (Data)

LOAD Country,

     Cust,

     Category,

     Status,

     Value,

     'Group2' AS GroupName

FROM

(ooxml, embedded labels, header is 1 lines, table is DummyData);

Concatenate (Data)

LOAD Country,

     Cust,

     Category,

     Sales,

     Desc,

     'Group3' AS GroupName

FROM

(ooxml, embedded labels, header is 1 lines, table is DummyData);

I can see that the DimA and ProdA didn't turn out exactly the same...I am trying to understandwhy Group 1 is for these two columns, but still the data in these two columns are categorized by all groups?

Best,

Ali A

[Gysbert_Wassenaar]

Perhaps like this:

Data:

LOAD

     Country,

     Cust,

     Category,

     DimA,

     ProdA,

     'Group1' as GroupName

FROM

     ...excel file...

CONCATENATE (Data)

LOAD

     Country,

     Cust,

     Category,

     DimA,

     ProdA,

     Status,

     Value,

     'Group2' as GroupName

FROM

     ...excel file...

CONCATENATE (Data)

LOAD

     Country,

     Cust,

     Category,

     DimA,

     ProdA,

     Sales,

     Desc,

     'Group3' as GroupName

FROM

     ...excel file...

[arvind1494]

Hi Areths,

I did this for you.

please download this qvw and check the solution.