Discussion Board for collaboration on QlikView Scripting.
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
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.
Go to Solution.
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
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);
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_compFROM(ooxml, embedded labels, table is Sheet1);let v_count=NoOfRows('input');
for i=0 to NoOfRows('input')-1
let v_quarter=peek('i_quarter',$(i),'input');let v_bu=peek('i_comp',$(i),'input');
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
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
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.
thanks, works perfectly!