Load different columns from multiple excel files?

[#1]

Hi All,

          I have yearwise 4 excel files. Every file having current year and previous year columns. for eg: for 2011 there is one column "Value 2011".similarly 2012 having columns "Value 2011" & "Value 2012"and so on.

How can we load this excel in a single load statement so that next year file Qlikview can automatically read the additional column?

Please help me in this...

Thanks,

Amruta

[#2]

Hi Satyadev,

Here i attach some excel files without data.

Please check and let me know.

Thanks,

Amruta

[Anonymous]

See attachment.

Thanks

[MarcoWedel · Accepted solution]

Hi Amruta,

another solution could be:

table1:
CrossTable(Year, [Total number of Hours], 9)
LOAD AutoNumberHash128(FileBaseName(), RecNo()) as RecID,*
FROM 
(ooxml, embedded labels, table is Sheet1);

table2:
LOAD RecID,
     Right(Year, 4)as Year,
     [Total number of Hours]
Resident table1;

DROP Fields Year, [Total number of Hours] From table1;

table3:
NoConcatenate
LOAD Distinct *
Resident table1;

DROP Table table1;

pivot table:

I used a version of your excel files with reduced column numbers and some random data.

You would have to adapt the crosstable load accordingly.

hope this helps

regards

Marco

[#5]

Hi Marco Wedel,

     Thanks for your help !!!

     Now its working..I am able to load all the files in a single load.

Thanks,

Amruta