topic Re: Case statement in QlikView

Case statement

Anonymous — Tue, 10 Feb 2015 14:17:35 GMT

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 — Tue, 10 Feb 2015 15:14:32 GMT

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 — Tue, 10 Feb 2015 15:22:35 GMT

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 — Tue, 10 Feb 2015 15:24:03 GMT

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