Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have scenario where i have defined 8 variables in qlik
1)4 quarter start dates
2)4 quarter end dates
I have clubbed them using set function
set QtrStart_date_Table = '$(var1_prev_first_qtr_end_date)','$(var2_prev_second_qtr_end_date)'...... set QtrEnd_date_Table = '$(var1_prev_first_qtr_end_date)','$(var2_prev_second_qtr_end_date)','$(var3_prev_third_qtr_end_date)'.........
now I want to execute a load script for 4 times passing a set of start date and end date
The script I tried to write in load editor
a =1 Do while a<4 set qtr_strt = $(QtrStart_date_Table[a]); set qtr_end= =$(set QtrEnd_date_Table[a]); load ... ...... select * from xvz table where QTR_ST_DT = $(qtr_strt) let a=a+1; loop
I know the method of picking the value from list is wrong. What could be alternative to this?
for each will only work for one set of values. How to use it for passing 2 concurrent values
Instead of storing them all in a single variable... what if you do something like this
SET QtrStart_date_Table1 = '$(var1_prev_first_qtr_end_date)'; SET QtrStart_date_Table2 = '$(var2_prev_second_qtr_end_date); ... SET QtrEnd_date_Table1 = '$(var1_prev_first_qtr_end_date)'; SET QtrEnd_date_Table2 = '$(var2_prev_second_qtr_end_date)'; SET QtrEnd_date_Table3 = '$(var3_prev_third_qtr_end_date); ...
and then read it like this
FOR i = 1 to 4 LET qtr_strt = $(QtrStart_date_Table$(i)); LET qtr_end= =$(set QtrEnd_date_Table$(i)); LOAD ... SELECT * FROM xvz_table WHERE QTR_ST_DT = '$(qtr_strt)' NEXT
Instead of storing them all in a single variable... what if you do something like this
SET QtrStart_date_Table1 = '$(var1_prev_first_qtr_end_date)'; SET QtrStart_date_Table2 = '$(var2_prev_second_qtr_end_date); ... SET QtrEnd_date_Table1 = '$(var1_prev_first_qtr_end_date)'; SET QtrEnd_date_Table2 = '$(var2_prev_second_qtr_end_date)'; SET QtrEnd_date_Table3 = '$(var3_prev_third_qtr_end_date); ...
and then read it like this
FOR i = 1 to 4 LET qtr_strt = $(QtrStart_date_Table$(i)); LET qtr_end= =$(set QtrEnd_date_Table$(i)); LOAD ... SELECT * FROM xvz_table WHERE QTR_ST_DT = '$(qtr_strt)' NEXT