topic Re: Loading different formate excel into Qlikview in QlikView

Loading different formate excel into Qlikview

Anonymous — Tue, 23 Jun 2015 12:46:00 GMT

Hi All,

I have excel file with attached formate.I have to load that file into following imag formate..

Can any one explain me step by step..

Thanks,

Re: Loading different formate excel into Qlikview

MK_QSL — Tue, 23 Jun 2015 12:57:06 GMT

multi_header_pivot_import.qvw

Re: Loading different formate excel into Qlikview

giakoum — Tue, 23 Jun 2015 12:57:38 GMT

I would load the data first assuming that columns are fixed (2 columns per month, months in order) and then crosstable the result to make it more like a fact table.

see attached

Re: Loading different formate excel into Qlikview

Tue, 23 Jun 2015 13:44:16 GMT

Hi,

When loading the data from excel sheet use under labels-> explicit labels

after that under Header size-> Lines-> change that 0 to 1 -> than click on finish u can see the o/p as

LOAD id,

Name,

No,

[project value],

efforts,

[project value1],

efforts1

FROM

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

after that according to u r requirement you can change like this

LOAD id,

Name,

No,

[project value] as [Jan project value ],

efforts as [Jan efforts ],

[project value1] as [Feb project value1] ,

efforts1 as [Feb efforts1]

FROM

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

with the help of straight table you can see u r o/p

thank you,

balaji.k

Re: Loading different formate excel into Qlikview

Peter_Cammaert — Tue, 23 Jun 2015 13:45:10 GMT

Step-by-step?

First copy this piece of code into your load script:

LET vStartDate = '1/1/2015';

RawData:

CROSSTABLE(FName, Data, 3)

LOAD * FROM [sample.xlsx](ooxml, embedded labels, header is 1 lines, table is Sheet1);

FinalTable:

NOCONCATENATE

LOAD id, Name, No,

dual

( Date(AddMonths('$(vStartDate)',alt(num#(KeepChar(FName,'1234567890')),0)),'MMM ')

& PurgeChar(FName, '1234567890'),

Date(AddMonths('$(vStartDate)',alt(num#(KeepChar(FName,'1234567890')),0)),'MMM ')*2

+ IF (left(FName, 3) = 'eff', 1, 0)

) AS Label,

Data

RESIDENT RawData;

DROP Table RawData;

Note: this will pick up any additional columns you may add in the future.

Next, Reload

Then, create a pivot with dimensions id, Name, No, and Label. Add a single expression =Sum(Data) Do not forget to add =' ' (a single space) as a custom label to dimension Label.

Finally, pivot the dimension Label so that it becomes a horizontal dimension above the expression.

You'll get something like:

Doc in attachment.

Best,

Peter