topic Re: How to write the Case statement with Coalesce function in Qlikview? in QlikView

How to write the Case statement with Coalesce function in Qlikview?

Anonymous — Tue, 20 Dec 2016 08:12:04 GMT

Hi,

Greetings for the day!

I am new to Qlikview and this blog. I am strucked with the below case statement to write in Qlikview. Though I have done my homework, couldn't find the correct solution.

Please help me to resolve this.

Case

When Start_DT is Null then Null

When Start_DT < '2015-03-01' and Coalesce(Start_DT,'1800-01-01') < '2016-10-25' Then 'Yes'

When Start_DT >= '2015-03-01' and Coalesce(Start_DT,'1800-01-01') < '2016-10-25' Then 'No'

END

Re: How to write the Case statement with Coalesce function in Qlikview?

Anil_Babu_Samineni — Tue, 20 Dec 2016 12:02:36 GMT

Try this, I heard Coalesce function is in Teradata which declares NVL in Oracle. Confirm the same and then try like below

If(Srart_DT = Null(), '',

If(Start_DT < MakeDate(2015-03-01) and If(WildMatch(Srart_DT, '1800-01-01') < MakeDate(2016-10-25)), 'Yes',

If(Start_DT >= MakeDate(2015-03-01) and If(WildMatch(Srart_DT, '1800-01-01') < MakeDate(2016-10-25)), 'No', '')))

Re: How to write the Case statement with Coalesce function in Qlikview?

sunny_talwar — Tue, 20 Dec 2016 12:16:17 GMT

May be this:

If(Len(Trim(Start_DT)) = 0, Null(),

If(Start_DT < MakeDate(2015, 3, 1) and Alt(Start_DT, MakeDate(1800, 1, 1)) < MakeDate(2016, 10, 25), 'Yes',

If(Start_DT >= MakeDate(2015, 3, 1) and Alt(Start_DT, MakeDate(1800, 1, 1)) < MakeDate(2016, 10, 25), 'No')))

Re: How to write the Case statement with Coalesce function in Qlikview?

Anonymous — Wed, 28 Dec 2016 05:55:19 GMT

HI Sunny,

I have tried the above, but getting the error like below

If takes 2-3 parameters

Re: How to write the Case statement with Coalesce function in Qlikview?

sunny_talwar — Wed, 28 Dec 2016 10:47:18 GMT

Can you share a screenshot of your expression?

Re: How to write the Case statement with Coalesce function in Qlikview?

johnw — Wed, 28 Dec 2016 20:23:28 GMT

It seems like your case can be simplified. You've already verified that Start_DT is null with your first condition, so then coalesce(Start_DT,'1800-01-01') seems like it would always return Start_DT. So your second condition is just checking that Start_DT < '2015-03-01' and Start_DT < '2016-10-25', but if the first part is true, the second part must also be true, so it simplifies further to just Start_DT < '2015-03-01'. Since that's our second condition, we'll only get to our third condition if it's >= '2015-03-01', so there's no need to check that, and the second part again simplifies to Start_DT < '2016-10-25'. So it seems your case statement reduces to this:

Case
When Start_DT is Null then Null
When Start_DT < '2015-03-01' Then 'Yes'
When Start_DT < '2016-10-25' Then 'No'
END

Now in QlikView, if Start_DT is null, then if we check that it's less than some value, this will be false, same as checking greater than a value. Null isn't a value. So in QlikView, we don't need to check that first condition. We can go straight to the date checks. I believe this would work.

if(Start_DT<makedate(2015, 3, 1),'Yes',

if(Start_DT<makedate(2016,10,25),'No'))