Need help in converting sql condition to qlik

[chinnu123]

Hi Experts,

I  need your help in converting sql condition to qlik.

Here is my condition  

CASE WHEN TRUNC(start_ date) < TO_DATE('2016-04-30 00:00:00','yyyy-mm-dd hh24:mi:ss') THEN  status_complete WHEN status_complete='Y' ANDCDD_COMP_FLAG=1 THEN 'X' ELSE '' END CDD_Complete

The above condition in sql I need to convert that into qlik. here start date,to_date and status_complete are the fields.

Can any one please help on this

Thanks in advance,

Thanks,

Chinnu.

[sunny_talwar · Accepted solution]

May be this

If(Floor(start_date) < MakeDate(2016, 4, 30), status_complete,

     If(status_complete = 'Y' and CDD_COMP_FLAG = 1, 'X', '')) AS CDD_Complete

[mjtaft2017]

I think this -- as this is in load script and you cannot set a field and test within same script

If(Floor(Start_date) < MakeDate(2016,4,30),

  If (CDD_COMP_FLAG=1, 'X', ' ')) AS CDD_Complete

[sunny_talwar]

And where exactly am I setting a field and testing within the same script? Not sure I understand?

[mjtaft2017]

essentially he wants to set status complete (to Y assuming) when start date < 4-30-2016 and then immediately check if status_complete = 'Y' & the CDD_COMP_FLAG= 1 ;  set CDD_Complete to 'X' otherwise ' '

that's why i nested it as the True to the If

[sunny_talwar]

I am not sure I follow.... based on what I am seeing in the Case statement...

CASE

     WHEN TRUNC(start_ date) < TO_DATE('2016-04-30 00:00:00','yyyy-mm-dd hh24:mi:ss') THEN  status_complete

     WHEN status_complete = 'Y' and CDD_COMP_FLAG = 1 THEN 'X'

     ELSE ''

END CDD_Complete

This to me looks like a nested if statement.... I am not sure I understand your logic.

[mjtaft2017]

I tried it both ways in the script - my version in TableB worked.  I got a script error on yours (TableA)

//TableA:

//NoConcatenate

//Load CloseDate, IsClosed,

//If(Floor(CloseDate) < MakeDate(2017, 12, 30), status_complete,

//    If(status_complete = 'Y' and IsClosed = 1, 'X', '')) AS CDD_Complete

//FROM [..\..\..\Downloads\Opportunity1.qvd] (qvd) ;

This works ---

TableB:

NoConcatenate

Load CloseDate, IsClosed,

If(Floor(CloseDate) < MakeDate(2017, 12, 30),

    If(IsClosed = 1, 'X', '')) AS CDD_Complete

FROM [..\..\..\Downloads\Opportunity1.qvd] (qvd) ;

therefore in his script - this will work for his load statement:

If(Floor(Start_date) < MakeDate(2016,4,30),

  If (CDD_COMP_FLAG=1, 'X', ' ')) AS CDD_Complete

[sunny_talwar]

Ma'am -

I am not sure what you are trying to do... but it appears that you don't have a fieldname called status_complete in your table... where as this is something which is available for OP.

I think I am not going to argue with you because I have not seen the data myself. But I gave a Qlik equivalent of the case statement I saw.

Best,

Sunny

[mjtaft2017]

Sunny -

I look up to you so please do not take my reply as anything other than that. No argument intended at all    

I see the error in my thinking - I made a false assumption and I am glad you pointed that out.  I humbly apologize.

[sunny_talwar]

You don't have to apologize for anything. We don't even know and probably you are right in what you want pointed out. I am just trying to convey that there is no point us talking about this when neither of us have seen the actual data here .

I appreciate your effort to learn and contribute to this community and I hope you will continue it for a long time.

Best,

Sunny