topic Re: Excel Data transformation in QlikView

Excel Data transformation

arethaking — Wed, 25 Nov 2020 16:16:04 GMT

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:

Re: Excel Data transformation

olivierrobin — Tue, 14 Aug 2018 06:56:20 GMT

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);

Re: Excel Data transformation

Neymar_Jr — Tue, 14 Aug 2018 07:02:24 GMT

See if this helps - multi_header_pivot_import.qvw

Thanks,

Re: Excel Data transformation

bwisealiahmad — Tue, 14 Aug 2018 07:09:10 GMT

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

Re: Excel Data transformation

Gysbert_Wassenaar — Tue, 14 Aug 2018 07:33:16 GMT

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...

Re: Excel Data transformation

arvind1494 — Tue, 14 Aug 2018 07:51:54 GMT

Hi Areths,

I did this for you.

please download this qvw and check the solution.