Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
maxgro
MVP
MVP

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
Anonymous
Not applicable
Author

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

maxgro
MVP
MVP

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

MarcoWedel

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
Author

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
Author

Dear maxgro,

thanks, works perfectly!

BR

Vitalii