Two Dates in Same Expression

[raghvendrasingh]

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.

[Anil_Babu_Samineni]

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

[raghvendrasingh]

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.

[Anil_Babu_Samineni]

May be look

Using Multiple Dates With Master Calendar

And instead of Dynamic May be write GetFieldSelection(COS_REC_APPR_DT)

[jonathandienst]

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.

[jonathandienst]

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

[raghvendrasingh]

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.