Loading different formate excel into Qlikview

[Anonymous]

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,

[MK_QSL]

multi_header_pivot_import.qvw

[giakoum]

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

[#4]

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

[Peter_Cammaert]

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