topic Re: Problum in Loading multiple excel files in QlikView

Problum in Loading multiple excel files

Tue, 24 Feb 2015 08:18:15 GMT

Hi,

I am receiving daily production report from the users in below format and file name.

"Production Report of jan.xls"

ID	Name	01/01/2015	01/02/2015	01/03/2015
1	a	10	90	20
2	b	20	46	30
3	c	30	45	98

I am going to get same format data for February also .And file name will be "Production Report of Feb.xls"

Here the problem is i can load all the excel file from a folder which is having same nomenclature and header but in my case first and second column will have the same header but from third column it won't as from here date is going to start.

I am using crosstable to making date as a field in Qlikview.

Please advise how can i resolve this issue.

Your help on this regard will be appreciated.

Thanks & regards,

Himanshu

Re: Problum in Loading multiple excel files

Tue, 24 Feb 2015 08:23:15 GMT

Himanshu could you clear your problem statement. I am not able to understand what is your problem.

Re: Problum in Loading multiple excel files

Tue, 24 Feb 2015 08:39:18 GMT

I want to load all excel file from a folder by single load statement.

"Production Report of jan.xls"

"Production Report of feb.xls"

"Production Report of march.xls"

"Production Report of Apr.xls"

These files consisting date wise production data as below format.

Jan month file look like this -

ID	Name	01/01/2015	01/02/2015	01/03/2015
1	a	10	90	20
2	b	20	46	30
3	c	30	45	98

Feb month file look like this -

ID	Name	02/01/2015	02/02/2015	02/03/2015
1	a	10	90	20
2	b	20	46	30
6	t	30	45	98
4	d	40	20	35

Re: Problum in Loading multiple excel files

Tue, 24 Feb 2015 08:47:22 GMT

Okk....

You should check out this thread...and i think you could manage to make some changes by applying an additional for loop in the value part so as to loop through the diffrerent values.

http://community.qlik.com/docs/DOC-1306

Re: Problum in Loading multiple excel files

Tue, 24 Feb 2015 09:10:00 GMT

Hi Himanshu,

if your underlying excels have the same first two fields like that, then you shouldn't need to loop and can just use a crosstable.

TableName:

Crosstable(Date,Value,2)

Load *

From [Production Report of*].xls

the wildcard star will load all the excel and as the first two fields are the same in each excel followed by your date columns, the load will auto concatenate all the excel for you.

This does rely on the excels being in the same folder.

hope that helps

Joe

Re: Problum in Loading multiple excel files

Tue, 24 Feb 2015 09:20:12 GMT

Hi Joe,

You are right.

This is really a good solution.

Re: Problum in Loading multiple excel files

Tue, 24 Feb 2015 10:22:22 GMT

Thank very much Joe it is working fine I got exactly what i need

Re: Problum in Loading multiple excel files

Tue, 24 Feb 2015 10:34:11 GMT

No problem, glad to help