topic Re: Make cases in my script in QlikView

Make cases in my script

Mon, 26 Nov 2012 17:00:34 GMT

Hi!

I have the field. [In_Order Date] from an imported table in my script.

LOAD InvoiceNo,

[In_Line No_],

[In_Document No_],

[In_Order No_],

[In_Order Date],

[In_Posting Date]

FROM

[..]

(qvd);

I want to make some cases out of this field.

I would like to have cases for:

when [In_Order Date] between 2011-11-26 and 2012-11-26 then Active

when [In_Order Date] between 2009-11-26 and 2011-11-26 then Passive

when [In_Order Date] older than 2009-11-26 then Passive old

and i want this extra field to be named (as?) "Customer_type".

How do i do it?

/Julia

Re: Make cases in my script

Gysbert_Wassenaar — Mon, 26 Nov 2012 17:06:19 GMT

Try:

if([In_Order Date]> addyears(today(),-1) and [In_Order Date] <= today(),'Active',

if([In_Order Date]<= addyears(today(),-1) and [In_Order Date]> addyears(today(),-2), 'Passive',

if([In_Order Date]<= addyears(today(),-2), 'Passive_Old',

if(len(trim([In_Order Date]))=0, 'No')))) as Customer_type

I assume you want to use today() instead of fixed dates, but you can substitute today() with the fixed dates if you want: date#('2012-11-26','YYYY-MM-DD') etc.

Re: Make cases in my script

Mon, 26 Nov 2012 17:21:28 GMT

Nice! Thank you! It worked.

Exept from:

if(len(trim([In_Order Date])=0, 'No order')))) as Customer_type,

Can I write something else. For "No order" there should be no [In_Order date]

Can I write something similar to:

if ([In_Order Date])=' * ', 'No order')))) as Customer_type,

/Julia