topic Substitution in QlikView

Substitution

sagaraperera — Wed, 25 Nov 2020 16:16:04 GMT

Der all

I have got the following expression in a my script

Now I want to change det_ref_date and det_risk_date of '13-mar-18' I want to use a variable.

SELECT DET_DEBT_CODE,DEB_NAME,DET_BRANCH_CODE,det_rp_code,

SUM(CASE WHEN TO_date(:P_AS_AT_DATE,'dd-mon-yy') - to_date(REF_C_DATE) BETWEEN 0 AND 30 THEN NVL(txn_amt,0) END ) Days_0_30,

SUM(CASE WHEN TO_date(:P_AS_AT_DATE,'dd-mon-yy') - to_date(REF_C_DATE) BETWEEN 31 AND 60 THEN NVL(txn_amt,0) END ) Days_31_60,

SUM(CASE WHEN TO_date(:P_AS_AT_DATE,'dd-mon-yy') - to_date(REF_C_DATE) BETWEEN 61 AND 90 THEN NVL(txn_amt,0) END ) Days_61_90,

SUM(CASE WHEN TO_date(:P_AS_AT_DATE,'dd-mon-yy') - to_date(REF_C_DATE) BETWEEN 91 AND 120 THEN NVL(txn_amt,0) END ) Days_91_120,

SUM(CASE WHEN TO_date(:P_AS_AT_DATE,'dd-mon-yy') - to_date(REF_C_DATE) BETWEEN 121 AND 150 THEN NVL(txn_amt,0) END ) Days_121_150,

SUM(CASE WHEN TO_date(:P_AS_AT_DATE,'dd-mon-yy') - to_date(REF_C_DATE) BETWEEN 151 AND 180 THEN NVL(txn_amt,0) END ) Days_151_180,

SUM(CASE WHEN TO_date(:P_AS_AT_DATE,'dd-mon-yy') - to_date(REF_C_DATE) BETWEEN 181 AND 360 THEN NVL(txn_amt,0) END ) Days_181_360,

SUM(CASE WHEN TO_date(:P_AS_AT_DATE,'dd-mon-yy') - to_date(REF_C_DATE) >360 THEN NVL(txn_amt,0) END ) Days_Over_360

FROM (　

select a.DET_DEBT_CODE,

( select DEB_NAME_1 from ac_m_debtor where DEB_BRANCH_CODE=a.DET_BRANCH_CODE

and DEB_DEBTOR_CODE=a.DET_DEBT_CODE ) DEB_NAME,

a.DET_BRANCH_CODE,

a.DET_REF_C ,

det_risk_date REF_C_DATE,

a.det_contra_acct DET_REF_DATE,

( select sum(decode(det_dc_code,'D',det_hc_amt,'C',-det_hc_amt)) from ac_t_debtor c where a.det_ref_c=c.det_ref_c

and a.det_branch_code=c.det_branch_code and c.det_ref_date<=TO_date(:P_PROG_DATE,'dd-mon-yy') ) Txn_Amt,

a.DET_TXNTYPE,

a.DET_REF_2,

A.det_rp_code

from ac_t_debtor a

where a.det_ref_date<=TO_date(:P_AS_AT_DATE,'dd-mon-yy')

---and A.DET_DEBT_CODE between :P_CR_FROM and :P_CR_TO

and a.det_risk_date<=TO_date(:P_AS_AT_DATE,'dd-mon-yy')

and a.det_txntype='1'

and a.DET_BRANCH_CODE LIKE 'CO%'

order by A.DET_DEBT_CODE

)

WHERE txn_amt !=0

GROUP BY DET_DEBT_CODE,DEB_NAME,DET_BRANCH_CODE,det_rp_code

ORDER BY DET_DEBT_CODE;

saga

jwjackso — Thu, 15 Mar 2018 19:20:21 GMT

In your script set your variable:

LET vdet_risk_date = Date(Now(),'DD-MMM-YY');

In your SQL:

and a.det_risk_date<='$(vdet_risk_date)'

sasiparupudi1 — Fri, 16 Mar 2018 14:00:16 GMT

Let vDate=Date(Date#('13-mar-18','DD-MMM-YY'),'DD-MMM-YY');