Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
raghvendrasingh
Creator II
Creator II

Two Dates in Same Expression

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.

6 Replies
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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
raghvendrasingh
Creator II
Creator II
Author

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)

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
jonathandienst
Partner - Champion III
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
raghvendrasingh
Creator II
Creator II
Author

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.