Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have following script in my QV document
SET T_YEAR=2008;
...................
TIME:
load *,
if(match(SEQ,'7','8','11'),MINUTES*(DIR_RECEIPTS+DS_RECEIPTS),
if(match(SEQ,'9'),MINUTES*DEBIT_NOTES,
if(match(SEQ,'10'),MINUTES*CREDIT_NOTES,
if(match(SEQ,'12'),MINUTES*CALIM_VOUCHERS,
if(match(SEQ,'16','19'),MINUTES*CALIM_PAY_NOS,
if(match(SEQ,'17','20'),MINUTES*COM_PAY_NOS,
if(match(SEQ,'14'),MINUTES*COM_ENT,
if(match(SEQ,'15'),MINUTES*COM_REV,
if(match(SEQ,'48'),MINUTES*RTN_ADV,
if(match(SEQ,'49'),MINUTES*CAN_ADV,
if(match(SEQ,'18','21'),MINUTES*SUNDRY_PAYMENT,
if(match(SEQ,'27','28'),MINUTES*JOURNAL_ENTRY
,MINUTES))))))))))))
as TOT_MINUTES
Resident TEMP;
store TIME into d:\staff_volume\qvd\TIME$(T_YEAR).qvd;
DROP Table TEMP;
I want to run the above script for T_YEAR from 2008 to 2016 through a loop pls help me to modify my script
You don't have any where statement in there? You want to save the same exact date for each year from 2008 till 2016?
yes
May be this:
...................
TIME:
LOAD *,
if(match(SEQ,'7','8','11'),MINUTES*(DIR_RECEIPTS+DS_RECEIPTS),
if(match(SEQ,'9'),MINUTES*DEBIT_NOTES,
if(match(SEQ,'10'),MINUTES*CREDIT_NOTES,
if(match(SEQ,'12'),MINUTES*CALIM_VOUCHERS,
if(match(SEQ,'16','19'),MINUTES*CALIM_PAY_NOS,
if(match(SEQ,'17','20'),MINUTES*COM_PAY_NOS,
if(match(SEQ,'14'),MINUTES*COM_ENT,
if(match(SEQ,'15'),MINUTES*COM_REV,
if(match(SEQ,'48'),MINUTES*RTN_ADV,
if(match(SEQ,'49'),MINUTES*CAN_ADV,
if(match(SEQ,'18','21'),MINUTES*SUNDRY_PAYMENT,
if(match(SEQ,'27','28'),MINUTES*JOURNAL_ENTRY
,MINUTES))))))))))))
as TOT_MINUTES
Resident TEMP;
FOR T_YEAR = 2008 TO 2016
STORE TIME into d:\staff_volume\qvd\TIME$(T_YEAR).qvd;
NEXT
DROP Table TEMP;
Although, I have no idea why you would do this, but the above should work
Thanks Sunny
my full script is as follows
SET T_YEAR=2016;
emp:
LOAD EMPLOYEE_NAME,
Sex,
Civic_Status,
Designation,
Highest_educational_qualification,
Highest_Professional_qualification,
Present_Salary,
// Attached_Branch_Code,
[Age in Months_as_at_31_12_16],
SERVICE,
SEQ,
MAIN_TYPE,
DESCRIPTION,
Frequency,
Time_Consumption_In_Realtion_To,
MINUTES,
IND_MINUTES,
DES,
BCOD
FROM
D:\Staff_volume\qvd\EMP_VOLUME.QVD
(qvd)
;
dirreceipt: Mapping LOAD BCOD,NOS from D:\Staff_volume\qvd\TOT_VOLUME.QVD(qvd)where TYPE='DIR_RECEIPTS' AND YEAR(TRN_DATE)=$(T_YEAR);
dsrreceipt: Mapping LOAD BCOD,NOS from D:\Staff_volume\qvd\TOT_VOLUME.QVD(qvd)where TYPE='DS_RECEIPTS' AND YEAR(TRN_DATE)=$(T_YEAR);
debitnote: Mapping LOAD BCOD,NOS from D:\Staff_volume\qvd\TOT_VOLUME.QVD(qvd)where TYPE='DEBIT_NOTES' AND YEAR(TRN_DATE)=$(T_YEAR);
creditnote: Mapping LOAD BCOD,NOS from D:\Staff_volume\qvd\TOT_VOLUME.QVD(qvd)where TYPE='CREDIT_NOTES' AND YEAR(TRN_DATE)=$(T_YEAR);
claimpay: Mapping LOAD BCOD,NOS from D:\Staff_volume\qvd\TOT_VOLUME.QVD(qvd)where TYPE='CLAIM_PAYMENT'AND YEAR(TRN_DATE)=$(T_YEAR);
claimnos: Mapping LOAD BCOD,NOS from D:\Staff_volume\qvd\TOT_VOLUME.QVD(qvd)where TYPE='CLAIM_PAY_NOS' AND YEAR(TRN_DATE)=$(T_YEAR);
//sundrypay: Mapping LOAD BCOD,NOS from D:\Staff_volume\qvd\TOT_VOLUME.QVD(qvd)where TYPE='SUNDRY_PAY_NOS' AND YEAR(TRN_DATE)=$(T_YEAR);
compay: Mapping LOAD BCOD,NOS from D:\Staff_volume\qvd\TOT_VOLUME.QVD(qvd)where TYPE='COMM_PAYMENT' AND YEAR(TRN_DATE)=$(T_YEAR);
coment: Mapping LOAD BCOD,NOS from D:\Staff_volume\qvd\TOT_VOLUME.QVD(qvd)where TYPE='COMM_ENTRIES' AND YEAR(TRN_DATE)=$(T_YEAR);
comrev: Mapping LOAD BCOD,NOS*.07 from D:\Staff_volume\qvd\TOT_VOLUME.QVD(qvd)where TYPE='COMM_ENTRIES' AND YEAR(TRN_DATE)=$(T_YEAR);
rtnchq: Mapping LOAD BCOD,NOS from D:\Staff_volume\qvd\TOT_VOLUME.QVD(qvd)where TYPE='RTN_CHEQUE' AND YEAR(TRN_DATE)=$(T_YEAR);
canadv: Mapping LOAD BCOD,NOS from D:\Staff_volume\qvd\TOT_VOLUME.QVD(qvd)where TYPE='NPCREDIT_NOTES' AND YEAR(TRN_DATE)=$(T_YEAR);
sunpay: Mapping LOAD BCOD,NOS from D:\Staff_volume\qvd\TOT_VOLUME.QVD(qvd)where TYPE='SUNDRY_PAYMENT'AND YEAR(TRN_DATE)=$(T_YEAR);
jentry: Mapping LOAD BCOD,NOS from D:\Staff_volume\qvd\TOT_VOLUME.QVD(qvd)where TYPE='JOURNAL_ENTRY'AND YEAR(TRN_DATE)=$(T_YEAR);
TEMP:
load *,if(match(SEQ,'7','8','11'),ApplyMap('dirreceipt', BCOD,0))AS DIR_RECEIPTS;
load *,if(match(SEQ,'7','8','11'),ApplyMap('dsrreceipt', BCOD,0))AS DS_RECEIPTS;
load *,if(match(SEQ,'9'),ApplyMap('debitnote', BCOD,0))AS DEBIT_NOTES;
load *,if(match(SEQ,'10'),ApplyMap('creditnote', BCOD,0))AS CREDIT_NOTES;
load *,if(match(SEQ,'12'),ApplyMap('claimpay', BCOD,0))AS CALIM_VOUCHERS;
load *,if(match(SEQ,'16','19'),ApplyMap('claimpay', BCOD,0))AS CALIM_PAY_NOS;
load *,if(match(SEQ,'17','20'),ApplyMap('compay', BCOD,0))AS COM_PAY_NOS;
load *,if(match(SEQ,'14'),ApplyMap('coment', BCOD,0)) AS COM_ENT;
load *,if(match(SEQ,'15'),ApplyMap('comrev', BCOD,0))AS COM_REV;
load *,if(match(SEQ,'48'),ApplyMap('rtnchq', BCOD,0))AS RTN_ADV;
load *,if(match(SEQ,'49'),ApplyMap('canadv', BCOD,0))AS CAN_ADV;
load *,if(match(SEQ,'18','21'),ApplyMap('sunpay', BCOD,0))AS SUNDRY_PAYMENT;
load *,Date(yearEnd(Date#($(T_YEAR),'YYYY')),'DD-MMM-YYYY')AS [TRN_DATE],if(match(SEQ,'27','28'),ApplyMap('jentry', BCOD,0))AS JOURNAL_ENTRY
Resident emp
;
DROP Table emp;
TIME:
load *,
if(match(SEQ,'7','8','11'),MINUTES*(DIR_RECEIPTS+DS_RECEIPTS),
if(match(SEQ,'9'),MINUTES*DEBIT_NOTES,
if(match(SEQ,'10'),MINUTES*CREDIT_NOTES,
if(match(SEQ,'12'),MINUTES*CALIM_VOUCHERS,
if(match(SEQ,'16','19'),MINUTES*CALIM_PAY_NOS,
if(match(SEQ,'17','20'),MINUTES*COM_PAY_NOS,
if(match(SEQ,'14'),MINUTES*COM_ENT,
if(match(SEQ,'15'),MINUTES*COM_REV,
if(match(SEQ,'48'),MINUTES*RTN_ADV,
if(match(SEQ,'49'),MINUTES*CAN_ADV,
if(match(SEQ,'18','21'),MINUTES*SUNDRY_PAYMENT,
if(match(SEQ,'27','28'),MINUTES*JOURNAL_ENTRY
,MINUTES))))))))))))
as TOT_MINUTES
Resident TEMP;
store TIME into d:\staff_volume\qvd\TIME$(T_YEAR).qvd;
DROP Table TEMP;
Now where should I insert your script
May be something like this:
FOR T_YEAR = 2008 TO 2016
emp:
LOAD EMPLOYEE_NAME,
Sex,
Civic_Status,
Designation,
Highest_educational_qualification,
Highest_Professional_qualification,
Present_Salary,
// Attached_Branch_Code,
[Age in Months_as_at_31_12_16],
SERVICE,
SEQ,
MAIN_TYPE,
DESCRIPTION,
Frequency,
Time_Consumption_In_Realtion_To,
MINUTES,
IND_MINUTES,
DES,
BCOD
FROM
D:\Staff_volume\qvd\EMP_VOLUME.QVD
(qvd);
dirreceipt: Mapping LOAD BCOD,NOS from D:\Staff_volume\qvd\TOT_VOLUME.QVD(qvd)where TYPE='DIR_RECEIPTS' AND YEAR(TRN_DATE)=$(T_YEAR);
dsrreceipt: Mapping LOAD BCOD,NOS from D:\Staff_volume\qvd\TOT_VOLUME.QVD(qvd)where TYPE='DS_RECEIPTS' AND YEAR(TRN_DATE)=$(T_YEAR);
debitnote: Mapping LOAD BCOD,NOS from D:\Staff_volume\qvd\TOT_VOLUME.QVD(qvd)where TYPE='DEBIT_NOTES' AND YEAR(TRN_DATE)=$(T_YEAR);
creditnote: Mapping LOAD BCOD,NOS from D:\Staff_volume\qvd\TOT_VOLUME.QVD(qvd)where TYPE='CREDIT_NOTES' AND YEAR(TRN_DATE)=$(T_YEAR);
claimpay: Mapping LOAD BCOD,NOS from D:\Staff_volume\qvd\TOT_VOLUME.QVD(qvd)where TYPE='CLAIM_PAYMENT'AND YEAR(TRN_DATE)=$(T_YEAR);
claimnos: Mapping LOAD BCOD,NOS from D:\Staff_volume\qvd\TOT_VOLUME.QVD(qvd)where TYPE='CLAIM_PAY_NOS' AND YEAR(TRN_DATE)=$(T_YEAR);
//sundrypay: Mapping LOAD BCOD,NOS from D:\Staff_volume\qvd\TOT_VOLUME.QVD(qvd)where TYPE='SUNDRY_PAY_NOS' AND YEAR(TRN_DATE)=$(T_YEAR);
compay: Mapping LOAD BCOD,NOS from D:\Staff_volume\qvd\TOT_VOLUME.QVD(qvd)where TYPE='COMM_PAYMENT' AND YEAR(TRN_DATE)=$(T_YEAR);
coment: Mapping LOAD BCOD,NOS from D:\Staff_volume\qvd\TOT_VOLUME.QVD(qvd)where TYPE='COMM_ENTRIES' AND YEAR(TRN_DATE)=$(T_YEAR);
comrev: Mapping LOAD BCOD,NOS*.07 from D:\Staff_volume\qvd\TOT_VOLUME.QVD(qvd)where TYPE='COMM_ENTRIES' AND YEAR(TRN_DATE)=$(T_YEAR);
rtnchq: Mapping LOAD BCOD,NOS from D:\Staff_volume\qvd\TOT_VOLUME.QVD(qvd)where TYPE='RTN_CHEQUE' AND YEAR(TRN_DATE)=$(T_YEAR);
canadv: Mapping LOAD BCOD,NOS from D:\Staff_volume\qvd\TOT_VOLUME.QVD(qvd)where TYPE='NPCREDIT_NOTES' AND YEAR(TRN_DATE)=$(T_YEAR);
sunpay: Mapping LOAD BCOD,NOS from D:\Staff_volume\qvd\TOT_VOLUME.QVD(qvd)where TYPE='SUNDRY_PAYMENT'AND YEAR(TRN_DATE)=$(T_YEAR);
jentry: Mapping LOAD BCOD,NOS from D:\Staff_volume\qvd\TOT_VOLUME.QVD(qvd)where TYPE='JOURNAL_ENTRY'AND YEAR(TRN_DATE)=$(T_YEAR);
TEMP:
load *,if(match(SEQ,'7','8','11'),ApplyMap('dirreceipt', BCOD,0))AS DIR_RECEIPTS;
load *,if(match(SEQ,'7','8','11'),ApplyMap('dsrreceipt', BCOD,0))AS DS_RECEIPTS;
load *,if(match(SEQ,'9'),ApplyMap('debitnote', BCOD,0))AS DEBIT_NOTES;
load *,if(match(SEQ,'10'),ApplyMap('creditnote', BCOD,0))AS CREDIT_NOTES;
load *,if(match(SEQ,'12'),ApplyMap('claimpay', BCOD,0))AS CALIM_VOUCHERS;
load *,if(match(SEQ,'16','19'),ApplyMap('claimpay', BCOD,0))AS CALIM_PAY_NOS;
load *,if(match(SEQ,'17','20'),ApplyMap('compay', BCOD,0))AS COM_PAY_NOS;
load *,if(match(SEQ,'14'),ApplyMap('coment', BCOD,0)) AS COM_ENT;
load *,if(match(SEQ,'15'),ApplyMap('comrev', BCOD,0))AS COM_REV;
load *,if(match(SEQ,'48'),ApplyMap('rtnchq', BCOD,0))AS RTN_ADV;
load *,if(match(SEQ,'49'),ApplyMap('canadv', BCOD,0))AS CAN_ADV;
load *,if(match(SEQ,'18','21'),ApplyMap('sunpay', BCOD,0))AS SUNDRY_PAYMENT;
load *,Date(yearEnd(Date#($(T_YEAR),'YYYY')),'DD-MMM-YYYY')AS [TRN_DATE],if(match(SEQ,'27','28'),ApplyMap('jentry', BCOD,0))AS JOURNAL_ENTRY
Resident emp;
DROP Table emp;
TIME:
load *,
if(match(SEQ,'7','8','11'),MINUTES*(DIR_RECEIPTS+DS_RECEIPTS),
if(match(SEQ,'9'),MINUTES*DEBIT_NOTES,
if(match(SEQ,'10'),MINUTES*CREDIT_NOTES,
if(match(SEQ,'12'),MINUTES*CALIM_VOUCHERS,
if(match(SEQ,'16','19'),MINUTES*CALIM_PAY_NOS,
if(match(SEQ,'17','20'),MINUTES*COM_PAY_NOS,
if(match(SEQ,'14'),MINUTES*COM_ENT,
if(match(SEQ,'15'),MINUTES*COM_REV,
if(match(SEQ,'48'),MINUTES*RTN_ADV,
if(match(SEQ,'49'),MINUTES*CAN_ADV,
if(match(SEQ,'18','21'),MINUTES*SUNDRY_PAYMENT,
if(match(SEQ,'27','28'),MINUTES*JOURNAL_ENTRY,MINUTES)))))))))))) as TOT_MINUTES
Resident TEMP;
STORE TIME into d:\staff_volume\qvd\TIME$(T_YEAR).qvd;
DROP Table TEMP;
NEXT T_YEAR