Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Himanshu could you clear your problem statement. I am not able to understand what is your problem.
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 |
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.
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
Hi Joe,
You are right.
This is really a good solution.
Thank very much Joe it is working fine I got exactly what i need
No problem, glad to help