"For Each" parameters from flat file

[#1]

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

[Anonymous]

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 · Accepted solution]

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

[#5]

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

[#6]

Dear maxgro,

thanks, works perfectly!

BR

Vitalii