topic Re: "For Each" parameters from flat file in QlikView

"For Each" parameters from flat file

Thu, 03 Nov 2016 10:26:17 GMT

Dear colleagues,

I have the following routine:

For Each year in 2012, 2013, 2016

For Each quarter in 'Q1', 'Q2', 'Q3', 'Q4'

For Each comp in 33101, 46103, 66188

Export:

NoConcatenate Load

Resident XYZ

Where

Fisc_Year = '$(year)'

and match (Fisc_Quarter, '$(quarter)')

and business_unit = '$(comp)';

Store Export into export\Export_$(year)_$(quarter)_$(comp).csv (txt);

Drop Tables Export;

Question: How can I define the "For Each" parameters (marked red) in external flat file (lets say .xlsx), so that client does not have to go each time into a script editor and make changes?

Thank you very much in advance.

BR,

Vitalii

Re: "For Each" parameters from flat file

Anonymous — Thu, 03 Nov 2016 17:44:32 GMT

You can use the peek function to loop through the input file

test:
LOAD * INLINE [
    Fisc_Year , Fisc_Quarter, business_unit
    2012, Q1, 33101
    2013, Q2, 46103
    2014, Q3, 66188
];
NoConcatenate

input:
LOAD i_year,
i_quarter,
i_comp
FROM

(ooxml, embedded labels, table is Sheet1);
let v_count=NoOfRows('input');

for i=0 to NoOfRows('input')-1

let v_year=peek('i_year',i,'input');

let v_quarter=peek('i_quarter',$(i),'input');
let v_bu=peek('i_comp',$(i),'input');

Export:

NoConcatenate Load

Resident test

Where

Fisc_Year = $(v_year)

and match (Fisc_Quarter, $(v_quarter))

and business_unit = $(v_bu)
;

Store Export into C:\Export_$(v_year)_$(v_quarter)_$(v_bu).csv (txt);
Drop Tables Export;
Next

Re: "For Each" parameters from flat file

maxgro — Thu, 03 Nov 2016 18:16:42 GMT

Another option:

make an include file (vars.inc) with these lines

SET var1= 2012, 2013, 2016;

SET var2= 'Q1', 'Q2', 'Q3', 'Q4';

SET var3= 33101, 46103, 66188;

the script is

$(include=vars.inc)

For Each year in $(var1)

For Each quarter in $(var2)

For Each comp in $(var3)

// replace with your ...., this is just for test

trace $(var1) $(var2) $(var3);

Next

Re: "For Each" parameters from flat file

MarcoWedel — Fri, 04 Nov 2016 00:25:03 GMT

Hi,

maybe creating some combined year_quarter_comp key field from an Excel load, creating the temporary Export table using 'Keep' in one For...Next Loop getting all Keys using the FiledValue() function.

hope this helps

regards

Marco

Re: "For Each" parameters from flat file

Fri, 04 Nov 2016 09:48:37 GMT

Hi Vivek,

thanks for response.

Unfortunately that won't work, as far as there could be different number of rows in each field: e.g. 6 years, 4 quarters and only 2 divisions.

Thank you.

Vitalii

Re: "For Each" parameters from flat file

Fri, 04 Nov 2016 09:49:21 GMT

Dear maxgro,

thanks, works perfectly!

Vitalii