
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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', '')))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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')))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
HI Sunny,
I have tried the above, but getting the error like below
If takes 2-3 parameters

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Can you share a screenshot of your expression?


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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'))
