topic Re: loop in QlikView

loop

upaliwije — Sat, 31 Dec 2016 12:45:10 GMT

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

Re: loop

sunny_talwar — Sat, 31 Dec 2016 12:47:49 GMT

You don't have any where statement in there? You want to save the same exact date for each year from 2008 till 2016?

Re: loop

upaliwije — Sat, 31 Dec 2016 12:53:30 GMT

yes

Re: loop

sunny_talwar — Sat, 31 Dec 2016 12:57:03 GMT

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

Re: loop

upaliwije — Sat, 31 Dec 2016 13:23:30 GMT

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

Re: loop

sunny_talwar — Sat, 31 Dec 2016 15:28:29 GMT

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);