Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a database that I am trying to load all data from. This is payroll software, and every period the program recreates the same 16 tables.
For example, for this payroll period there is a table called REF01M_2012_01_Payslips (which refers to payroll account REF01, Year 2012, Period 01)
There are around 60 tables named like this for payslip data, going back 5 years. I don't want to have to manully select each one of these table in the load, and then have to go back every month and add in the newly added payroll.
In general programming terms, I understand that we could have a multi-dimensional loop which goes through all tables. The general format of the table names is REFXXM_YYYY_PP_Tablename, where XX in {01,02,03}, YYYY in {2007,2008,2009,2010,2011,2012} and PP in {01,02,03,04,05,06,07,08,09,10,11,12}. The tables themselves contain no datestamp, so I would need to include the variables in the load of the table
Therefore I think a loop like this would work
let XX = 01
where XX <= 03
let YYYY = 2007
Where YYYY <= 2012
let PP = 01
where PP <=12
vTable = "REF"&XX&"M_"&YYYY&PP&"_"
Load *,
PP as [Period],
YYYY as [Year],
XX as [Account Ref];
SQL Select * from vTable&"Payslips"
PP = PP+1
Loop
YYYY = YYYY+1
Loop
XX = XX +1
Loop
The problem is, I don't know how to write such a loop in the Qlikview script and my gut feeling is that this isn't the most effecent approach.
Could any one offer any pointers?
Thanks
Hello:
Following script would suit your requirement:
For Each XX In '01','02','03'
For YYYY = 2007 to 2012
For Each PP IN '01','02','03', '04','05','06', '07','08','09', '10', '11', '12'
Let vTable = 'REF'&$(XX)&'M_'&$(YYYY)&$(PP)&'_Payslips';
Trace $(vTable); // write the table name to log
$(vTable):
SQL Select * from $(vTable);
Store $(vTable) Into $(vTable).qvd (qvd);
Drop Table $(vTable);
Next
Next
Next
Thanks,
Narasimha K
Hello:
Following script would suit your requirement:
For Each XX In '01','02','03'
For YYYY = 2007 to 2012
For Each PP IN '01','02','03', '04','05','06', '07','08','09', '10', '11', '12'
Let vTable = 'REF'&$(XX)&'M_'&$(YYYY)&$(PP)&'_Payslips';
Trace $(vTable); // write the table name to log
$(vTable):
SQL Select * from $(vTable);
Store $(vTable) Into $(vTable).qvd (qvd);
Drop Table $(vTable);
Next
Next
Next
Thanks,
Narasimha K
Thats great thanks, exactly what I needed!