topic Trouble converting this case when statement in SQL to qlikview syntax in QlikView

Trouble converting this case when statement in SQL to qlikview syntax

Thu, 14 Apr 2011 18:50:28 GMT

Hi there,

Can someone help me translate the following case when statement in SQL to a syntaxt that qlikview will understand in the load statement? In the statement below TIME is my date field. YEAR is an nvarchar data type. An example of a value for YEAR is 'F2011'. I can't seem to get this to work in qlikview.

case when TIME in ('Jan','Feb','Mar')
then convert(smalldatetime,right(YEAR,4)+ TIME)
else dateadd(month,-12,convert(smalldatetime,right(YEAR,4)+ TIME))
end as Date

thanks in advance

Trouble converting this case when statement in SQL to qlikview syntax

erichshiino — Fri, 15 Apr 2011 02:50:42 GMT

Hi, Mike

TIME is a timestamp, right?

it would be something like this, inside a load statement.

LOAD ...

if( num(month(TIME))<3, MAKEDATE( RIGHT(YEAR,4), NUM( MONTH(TIME)) , DAY(TIME)),

ADDMONTHS( MAKEDATE( RIGHT(YEAR,4), NUM( MONTH(TIME)) , DAY(TIME)), -12) AS Date;

select ...

Tell me if if works for you data

Rgds,

Erich