topic Re: Load different columns from multiple excel files? in QlikView

Load different columns from multiple excel files?

Mon, 16 Jun 2014 02:40:55 GMT

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

Re: Load different columns from multiple excel files?

Mon, 16 Jun 2014 11:44:08 GMT

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.

Re: Load different columns from multiple excel files?

Mon, 16 Jun 2014 11:47:10 GMT

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

Re: Load different columns from multiple excel files?

Mon, 16 Jun 2014 12:41:37 GMT

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

));

Re: Load different columns from multiple excel files?

Tue, 17 Jun 2014 03:17:41 GMT

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

Re: Load different columns from multiple excel files?

Tue, 17 Jun 2014 03:21:10 GMT

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;

Re: Load different columns from multiple excel files?

Tue, 17 Jun 2014 03:30:16 GMT

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

Re: Load different columns from multiple excel files?

Anonymous — Tue, 17 Jun 2014 03:52:31 GMT

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

Re: Load different columns from multiple excel files?

Tue, 17 Jun 2014 04:21:13 GMT

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

Re: Load different columns from multiple excel files?

Anonymous — Tue, 17 Jun 2014 05:25:25 GMT

Hi,

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

Thanks

Re: Re: Load different columns from multiple excel files?

Tue, 17 Jun 2014 05:50:38 GMT

Hi Satyadev,

Here i attach some excel files without data.

Please check and let me know.

Thanks,

Amruta

Re: Re: Re: Load different columns from multiple excel files?

Anonymous — Tue, 17 Jun 2014 07:00:11 GMT

See attachment.

Thanks

Re: Load different columns from multiple excel files?

MarcoWedel — Tue, 17 Jun 2014 23:47:10 GMT

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

Re: Load different columns from multiple excel files?

Wed, 18 Jun 2014 04:03:47 GMT

Hi Marco Wedel,

Thanks for your help !!!

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

Thanks,

Amruta