Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
Not applicable

"For Each" parameters from flat file

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;


              Next

         Next

    Next

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

1 Solution

Accepted Solutions
MVP
MVP

Re: "For Each" parameters from flat file

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

    Next

Next

View solution in original post

5 Replies
viveknair1234
New Contributor III

Re: "For Each" parameters from flat file

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

MVP
MVP

Re: "For Each" parameters from flat file

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

    Next

Next

View solution in original post

Re: "For Each" parameters from flat file

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

Not applicable

Re: "For Each" parameters from flat file

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.

BR

Vitalii

Not applicable

Re: "For Each" parameters from flat file

Dear maxgro,

thanks, works perfectly!

BR

Vitalii