topic Re: Need help in converting sql condition to qlik in QlikView

Need help in converting sql condition to qlik

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

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.

Re: Need help in converting sql condition to qlik

sunny_talwar — Thu, 03 Aug 2017 12:44:14 GMT

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

Re: Need help in converting sql condition to qlik

mjtaft2017 — Thu, 03 Aug 2017 15:10:02 GMT

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

Re: Need help in converting sql condition to qlik

sunny_talwar — Thu, 03 Aug 2017 15:12:06 GMT

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

Re: Need help in converting sql condition to qlik

mjtaft2017 — Thu, 03 Aug 2017 15:20:57 GMT

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

Re: Need help in converting sql condition to qlik

sunny_talwar — Thu, 03 Aug 2017 15:26:09 GMT

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.

Re: Need help in converting sql condition to qlik

mjtaft2017 — Thu, 03 Aug 2017 16:56:12 GMT

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

Re: Need help in converting sql condition to qlik

sunny_talwar — Thu, 03 Aug 2017 17:00:03 GMT

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

Re: Need help in converting sql condition to qlik

mjtaft2017 — Thu, 03 Aug 2017 17:23:36 GMT

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.

Re: Need help in converting sql condition to qlik

sunny_talwar — Thu, 03 Aug 2017 17:49:41 GMT

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

Re: Need help in converting sql condition to qlik

chinnu123 — Fri, 04 Aug 2017 11:27:11 GMT

Hi Sunny,

Thanks for your Help.

Thanks,

Chinnu.