Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Case statement

Hi guys,

I have a case statement in SQL

  sum(CASE WHEN datediff(dd,due_date,getdate()) < = '0' then T1.unall_amount end) as 'Current',           

  sum(CASE WHEN datediff(dd,due_date,getdate()) between '1' and '30' then T1.unall_amount end) as '0-30',           

  sum(CASE WHEN datediff(dd,due_date,getdate()) between '31' and '60' then T1.unall_amount end) as '31-60',           

  sum(CASE WHEN datediff(dd,due_date,getdate()) between '61' and '90' then T1.unall_amount end) as '61-90',           

  sum(CASE WHEN datediff(dd,due_date,getdate()) between '91' and '120' then T1.unall_amount end) as '91-120',           

  sum(CASE WHEN datediff(dd,due_date,getdate()) >= '121' then T1.unall_amount end) as '121+'

Could I write this into a QV load script? Or even into an Expression would be better for me..

Unless this is best left in SQL load?

Any advise would be appreciated

3 Replies
Peter_Cammaert
Partner - Champion III
Partner - Champion III

Yes it can be done in the Load Script. However, a 1:1 translation is pretty much useless if there is no context.

Can you post the entire SQL SELECT/GROUP BY/Table JOIN statement?

Peter_Cammaert
Partner - Champion III
Partner - Champion III

In QlikView, you can do this like:

LOAD

:

IF ((OpenPeriod >= 1) and (OpenPeriod < 31), unall_amount) AS [0-30],

IF ((OpenPeriod >= 31) and (OpenPeriod < 61), unall_amount) AS [31-60],

IF ((OpenPeriod >= 61) and (OpenPeriod < 91), unall_amount) AS [61-90],

IF ((OpenPeriod >= 91) and (OpenPeriod < 121), unall_amount) AS [91-120],

IF ((OpenPeriod >= 121), unall_amount) AS [121+];

LOAD *,

     today() - due_date AS OpenPeriod;

SQL SELECT

:

Peter_Cammaert
Partner - Champion III
Partner - Champion III

Ok, my 2nd post doesn't contain any URLs or product names, but it's still being moderated. We'll have to be patient.