Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
I need your help.
I'm trying to read various xls files. They all have the same layout. I 'succeeded' to read one file, see attachment.
Problem / questions:
# 1. I would like to tilt the table so that every employee gets a separate row with values
# 2. Including showing the name of the employee, in stead of only Employee, Employee, etcetera.
# 3. I want to avoid having to copy more than 500 lines of code for each file. How can I read multiple file from the same folder? (for example by applying some kind of loop).
# 4. I suspect that this can be done smarter, by repeating each block (again by means of a kind of loop), whereby only the RecNo's have to be changed (since the rest remains more or less the same).
I would appreciate if you can edit and upload an example, working in the same (see example as attached) qvw.
Kind regards,
Eelco
I think it's a quite easy task and could be handled with a single-load statement for all data-parts and all files by using something like this:
set vPath = '.\*.xls';
set vCompany = 'Test-Company';
set vCountry = 'UK';
set vCurrency = 'Euro';
set vPeriod = 'JAN-JUN 2018';
MapRecNo:
mapping load * inline [
RecNo, Part
6, Restaurants
7, Restaurants
8, Restaurants
...
];
[Data]:
crosstable(Employee, Value, 9) LOAD
RecNo(),
RoWNo(),
'$(vCompany)' as Company,
'$(vCountry)' as Country,
'$(vCurrency)' as Currency,
'$(vPeriod)' as Period,
//'CREDITCARD' as Type,
//'Restaurants' as Part,
pick(match(recno(), 6,7,8, ...), 'Restaurants', 'Restaurants', 'Restaurants', ....) as Part,
applymap('MapRecNo', recno(), '#NV') as Part2,
@1 as Detail,
@4 AS Employee1,
@5 AS Employee2,
@6 AS Employee3,
@7 AS Employee4,
@8 AS Employee5,
@9 AS Employee6,
@10 AS Employee7,
@11 AS Employee8,
@12 AS Employee9,
@13 AS Employee10
FROM $(vPath) (biff, no labels, table is Total$)
WHERE
// RecNo()>5 and RecNo()<9 and
len(@1)>0;
This means using a wildcard * within the FilePath to load all files from the folder and matching the different data-parts to Part and Type categorizing with a pick(match()) or probably even better with a mapping-approach instead of repeating the load multiple times and controlling them per where-clause. The transformation from the Employee columns into rows is easily made with a crosstable-prefix.
- Marcus
Hi Eelco,
Is it compulsory that we can not maintain excel data first ?
Hi Quddus,
It's not entirely clear to me what you mean. Do you mean storing the data in the memory?
It will be ± 30 of these files that I have to read, so I think it is not that bad in terms of memory.
Hi Eelco,
I meant that can't we do some alteration in excel file before loading it?
I would preferably change as little as possible to the file. First I want to see if it is possible to write a script here. If it does not work, then there is nothing else, then I will indeed have to adjust the source files.
I think it's a quite easy task and could be handled with a single-load statement for all data-parts and all files by using something like this:
set vPath = '.\*.xls';
set vCompany = 'Test-Company';
set vCountry = 'UK';
set vCurrency = 'Euro';
set vPeriod = 'JAN-JUN 2018';
MapRecNo:
mapping load * inline [
RecNo, Part
6, Restaurants
7, Restaurants
8, Restaurants
...
];
[Data]:
crosstable(Employee, Value, 9) LOAD
RecNo(),
RoWNo(),
'$(vCompany)' as Company,
'$(vCountry)' as Country,
'$(vCurrency)' as Currency,
'$(vPeriod)' as Period,
//'CREDITCARD' as Type,
//'Restaurants' as Part,
pick(match(recno(), 6,7,8, ...), 'Restaurants', 'Restaurants', 'Restaurants', ....) as Part,
applymap('MapRecNo', recno(), '#NV') as Part2,
@1 as Detail,
@4 AS Employee1,
@5 AS Employee2,
@6 AS Employee3,
@7 AS Employee4,
@8 AS Employee5,
@9 AS Employee6,
@10 AS Employee7,
@11 AS Employee8,
@12 AS Employee9,
@13 AS Employee10
FROM $(vPath) (biff, no labels, table is Total$)
WHERE
// RecNo()>5 and RecNo()<9 and
len(@1)>0;
This means using a wildcard * within the FilePath to load all files from the folder and matching the different data-parts to Part and Type categorizing with a pick(match()) or probably even better with a mapping-approach instead of repeating the load multiple times and controlling them per where-clause. The transformation from the Employee columns into rows is easily made with a crosstable-prefix.
- Marcus
Hi Marcus,
Thanks for your response.
I get some errors when copying and running the code.
Can you upload a working example (qvw) for me?
Which errors?
Sorry, my mistake, The code is working now ...
Thank you very much.
I will immediately check if this is going to work for me / solves my problem.
Marcus, this works perfectly thanks.
Can you also show me how to display the employee names instead of 'Employee1', 'Employee2', etc.?