topic Re: Two Dates in Same Expression in QlikView

Two Dates in Same Expression

raghvendrasingh — Fri, 03 Mar 2017 06:25:43 GMT

Hi All Experts,

Below is the expression in Oracle-SQL:

--------------------------------------------------------------------------------------------

Select

SUM(CASE WHEN (TRUNC(COS_REC_APPR_DT) <= '31-Dec-16') AND

COS_EST_IND = '1' AND

(COS_EST_CLOSE_YN = '0' OR (COS_EST_CLOSE_YN = '1' AND TRUNC(COS_EST_CLOSE_DT) > '31-Dec-16'))

THEN

NVL(COS_REC_SIGN,1) *NVL(COS_EST_SIGN,1) *NVL(COS_OUR_SHARE_AMT_LC_1,0)

ELSE 0 END) LOSS_OS

from PGIS_CLM_OS

----------------------------------------------------------------------------------------

How can i make this in Qlikview, Because there are two dates used in same expression i.e. COS_REC_APPR_DT and COS_EST_CLOSE_DT.

On which date can i make the master calendar & make whole script in QV.

Kindly suggest.

Thanks in Advance.

Re: Two Dates in Same Expression

Anil_Babu_Samineni — Fri, 03 Mar 2017 06:34:41 GMT

May be try

If(TRUNC(COS_REC_APPR_DT) <= '31-Dec-16' and COS_EST_IND = '1' and COS_EST_CLOSE_YN = '0' or

(COS_EST_CLOSE_YN = '1' AND TRUNC(COS_EST_CLOSE_DT) > '31-Dec-16'),

Alt(If(isnull(COS_REC_SIGN) * IsNull(COS_EST_SIGN) * IsNull(COS_OUR_SHARE_AMT_LC_1)), 0))

Or Finally, create single date bu help of Flag and then use single Date field

Re: Two Dates in Same Expression

raghvendrasingh — Fri, 03 Mar 2017 06:41:55 GMT

Hi Anil,

Thanks For Reply

'31-Dec-16' this date is hard coded , it may be any date selected from end user and need to satisfy conditions with two dates COS_REC_APPR_DT and COS_EST_CLOSE_DT.

So how can i make the master calendar. because there are two dates used in same expression?

Kindly Suggest.

Re: Two Dates in Same Expression

Anil_Babu_Samineni — Fri, 03 Mar 2017 06:52:20 GMT

May be look

Using Multiple Dates With Master Calendar

And instead of Dynamic May be write GetFieldSelection(COS_REC_APPR_DT)

Re: Two Dates in Same Expression

jonathandienst — Fri, 03 Mar 2017 06:53:18 GMT

Why do you want to move this into QV? If you have a working SQL expression, then use it. Don;t fix it if it ain't broken.

LOAD *,

<qlikview based derived fields>

;

SQL

<existing SQL expression>

As to your question about which calendar, read Calendars for a comprehensive article about how to answer your question and many links to other resources.

Re: Two Dates in Same Expression

jonathandienst — Fri, 03 Mar 2017 06:57:54 GMT

>>31-Dec-16' this date is hard coded , it may be any date selected from end user and need to satisfy conditions with two dates COS_REC_APPR_DT and COS_EST_CLOSE_DT.

Let vDate = Date(<an expression based on your requirements>, 'dd-MMM-yyyy');

SQL SELECT

SUM(

CASE WHEN (TRUNC(COS_REC_APPR_DT) <= '$(vDate)') AND

COS_EST_IND = '1' AND

(COS_EST_CLOSE_YN = '0'

OR (COS_EST_CLOSE_YN = '1' AND TRUNC(COS_EST_CLOSE_DT) > '$(vDate)'))

THEN

NVL(COS_REC_SIGN,1) *NVL(COS_EST_SIGN,1) *NVL(COS_OUR_SHARE_AMT_LC_1,0)

ELSE 0 END) LOSS_OS

FROM PGIS_CLM_OS;

Re: Two Dates in Same Expression

raghvendrasingh — Fri, 03 Mar 2017 13:00:39 GMT

Hi Jonathan,

Here userv will not select value from InputBox/Variable. He will select year and Month from list box .

So how can we fetch dates from that.

Please suggest.