Case statement

[Anonymous]

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

[Peter_Cammaert]

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]

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]

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