topic Re: Creating a smart loop for reading Excel sheets with (almost) the same code in QlikView

Creating a smart loop for reading Excel sheets with (almost) the same code

cdss-developer — Tue, 07 Aug 2018 07:56:35 GMT

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

Re: Creating a smart loop for reading Excel sheets with (almost) the same code

quddus_mohiuddi — Tue, 07 Aug 2018 08:14:30 GMT

Hi Eelco,

Is it compulsory that we can not maintain excel data first ?

Re: Creating a smart loop for reading Excel sheets with (almost) the same code

cdss-developer — Tue, 07 Aug 2018 08:24:55 GMT

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.

Re: Creating a smart loop for reading Excel sheets with (almost) the same code

quddus_mohiuddi — Wed, 08 Aug 2018 09:10:25 GMT

Hi Eelco,

I meant that can't we do some alteration in excel file before loading it?

Re: Creating a smart loop for reading Excel sheets with (almost) the same code

cdss-developer — Fri, 10 Aug 2018 06:14:20 GMT

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.

Re: Creating a smart loop for reading Excel sheets with (almost) the same code

marcus_sommer — Fri, 10 Aug 2018 06:58:58 GMT

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

Re: Creating a smart loop for reading Excel sheets with (almost) the same code

cdss-developer — Fri, 10 Aug 2018 07:24:34 GMT

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?

Re: Creating a smart loop for reading Excel sheets with (almost) the same code

marcus_sommer — Fri, 10 Aug 2018 07:30:12 GMT

Which errors?

Re: Creating a smart loop for reading Excel sheets with (almost) the same code

cdss-developer — Fri, 10 Aug 2018 07:33:59 GMT

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.

Re: Creating a smart loop for reading Excel sheets with (almost) the same code

cdss-developer — Fri, 10 Aug 2018 07:45:54 GMT

Marcus, this works perfectly thanks.

Can you also show me how to display the employee names instead of 'Employee1', 'Employee2', etc.?

Re: Creating a smart loop for reading Excel sheets with (almost) the same code

marcus_sommer — Fri, 10 Aug 2018 08:22:07 GMT

You could within another load add/replace the generic employee-fields with an ID or a Name with the same mapping-approach like used for matching Part and Type whereby I assume that your different files contain different employees so you will need to combine Employee and the source to match them correctly. For this you could add filebasename() as Source within the first load (by adjusting the crosstable column-offset from 9 to 10 because there would be now one more field).

- Marcus