Make cases in my script

[#1]

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

[Gysbert_Wassenaar · Accepted solution]

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.

[#3]

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