Solved: Load different columns from multiple excel files? - Qlik Community

Report Inappropriate Content · ‎2014-06-15

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

MarcoWedel · ‎2014-06-17

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

View solution in original post

Report Inappropriate Content · ‎2014-06-16

Amruta, could you please provide more details like upload the source file for my investigation. It would be much easier to focus on the issues.

Report Inappropriate Content · ‎2014-06-16

Hi Amruta

a solution is either to take only the column according to the year of the xls file. So you name your fiels as year or take @n as year

second one is to take the last file and use cross table

best regards

Chris

Report Inappropriate Content · ‎2014-06-16

Load the files with labels to none and in transformation step delete the header row and name the columns manuall

This will work if the no of columns are same across your year wise xls.

Find the sample

LOAD @1 as Sno,

@2 as value,

@3 as Trigger

FROM

(ooxml, no labels, table is Sheet1, filters(

Remove(Row, Pos(Top, 1))

));

Report Inappropriate Content · ‎2014-06-16

Hi Uday Shankar,

Thanks for your help.

But the columns are not same across each files. As the data starts from 2011 so this file have column as Value 2011 and for 2012 file have the columns as Value 2011 and Value 2012 and so on.

Please help me in this...

Thanks,

Amruta

Report Inappropriate Content · ‎2014-06-16

Use the "as" identifier.

You can decide on a column name and rename the columns in the load statement.

e.g.

table1:

Load Value2001 as Value

from

file1;

Concatenate(table1)

Load Value2012 as Value from

file2;

Report Inappropriate Content · ‎2014-06-16

Hi Ramkumar,

I want to load all the files in a single load if 2015 data comes it will automatically load 2015 files.

Can you help me in this???

Thanks,

Amruta

Anonymous · ‎2014-06-16

Hi,

You may utilize below script. Please note, I have not tested this script as I don't have such a excel with me.

Value:

LOAD 2011 as Year,

0 as [LY Value],

[Value 2011] as [CY Value]

FROM 2011.xlsx

(ooxml, embedded labels, table is [Sheet1]);

FOR i = 2012 to Year(Today())

LOAD $(i) as Year,

'Value '&($(i)-1) as [LY Value],

'Value '&($(i)) as [CY Value]

FROM $(i).xlsx

(ooxml, embedded labels, table is [Sheet1]);

Report Inappropriate Content · ‎2014-06-17

Hi Satyadev,

I executed the script with your logic in a single load but the fields are not common across each file so it is giving error:'Field not found'

I am waiting for your help.

Thanks,

Amruta

Anonymous · ‎2014-06-17

Hi,

I am not sure which field you are talking about. Its better to send few excels without data to understand your query.

Thanks