topic Re: LOAD within for loop in QlikView

LOAD within for loop

AmCh — Fri, 15 Nov 2024 23:15:43 GMT

Hello all,

I have, for example, an xls. file with several sheets.

Each sheet contains the same tables structure (same number of columns and rows, they only differ in the cell values).

The sheets are named as follows: L_A, L_B, L_C

The fields have identical names in all sheets: x,y,z

Objective:

To load with for loop (incrementing in the list {A,B,C}) from the sheets (L_A, L_B, L_C) the fields (x,y,z) as (x_A,x_B,x_C,Y_A,..).

Can anyone help me?

Many thanks in advance

Re: LOAD within for loop

edwin — Sun, 24 Jan 2021 18:37:08 GMT

look at this post:

Solved: Load multiple sheets from Excel Files - Qlik Community - 1595476

Re: LOAD within for loop

AmCh — Sun, 24 Jan 2021 19:31:01 GMT

My imported fields should be imported separtely and not concatenated in one field ( e.g. Field a from Sheet 1 and field a from Sheet 2 should be imported as a1 from Sheet 1 and as a2 from Sheet 2).

(In my case not numerical index but the index should be taken from a list of names)

Re: LOAD within for loop

edwin — Sun, 24 Jan 2021 19:49:55 GMT

you can do however you want it, in you load statement, you can specify NOCONCATENATE and you can embed your sheetname into the field names, you can even use the variable in the tablename

for Each vSheet in 'A','B','C' NoConcatenate Sheet$(vSheet): LOAD x as x$(vSheet), y as y$(vSheet), z as z$(vSheet) FROM [file.xlsx] (ooxml, embedded labels, table is $(vSheet)); next;

Re: LOAD within for loop

edwin — Sun, 24 Jan 2021 19:53:02 GMT

obviously, if your sheet is L_A, L_B, etc..

use A, B, C as the variable value and just add L_ in the sheet name
... table is L_$(vSheet)
itll be easier