Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Live chat with experts, bring your API Integration questions. June 15th, 10 AM ET. REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
ksr24163
Contributor
Contributor

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

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

5 Replies
Anil_Babu_Samineni

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', '')))

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)
sunny_talwar

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')))

ksr24163
Contributor
Contributor
Author

HI Sunny,

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

If takes 2-3 parameters

sunny_talwar

Can you share a screenshot of your expression?

johnw
MVP
MVP

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'))