topic Re: If Statement in QlikView

If Statement

Thu, 07 Feb 2013 13:42:46 GMT

Hi,

I have "last_order_date" in my table.

For some customers this filed is empty. I.e. customer has not send any order ( - ).

Example:

customer last_order_date Status

1 - Passive

2 11-02-01 13:00:00 Passive

3 12-10-12 13:00:12 Active

4 - Passive

5 13-01-14 13:00:12 Active

The Status filed is created as:

if(last_order_date>Active_date, 'Active', if(last_order_date>Passive_date,'Passive','passive_not_customer')) as Status;

where

getdate()-366 as Active_date,

getdate()-365*3-1 as Passive_date,

How to I get the "passive_not_customer"- customers? Now they have status Passive, see customer 1 and 4!

It seems like it doesnt feel the emty field last_order_date for theese customers?

How do I do?

/Julia

Re: If Statement

Gabriel — Thu, 07 Feb 2013 13:48:46 GMT

Hi Julia,

Looking at the if expression, it practically does same thing.

if(last_order_date>Active_date, 'Active', if(last_order_date>Passive_date,'Passive','passive_not_customer')) as Status;

May be it will work if you change the > sign for the second if statement to <.

Let me know if it work

Regards,

Gabriel

Re: If Statement

Thu, 07 Feb 2013 13:54:16 GMT

No, then all the Passive Statuses turns into passive_not_customer.

And I only get Statuses: Active and passive_not_customer.

/Julia

Re: If Statement

Gysbert_Wassenaar — Thu, 07 Feb 2013 14:43:49 GMT

You could try using the rangesum function so if last_order_date is Null it returns 0. And 0 can be compared with Active_date or Passive_date in the if statements.

if(rangesum(0,last_order_date)>Active_date, 'Active', if(rangesum(0,last_order_date)>Passive_date,'Passive','passive_not_customer')) as Status;

Re: If Statement

Thu, 07 Feb 2013 16:48:09 GMT

Is this makes sense ?

if(IsNull(last_order_date), 'passive_not_customer', if(last_order_date>Active_date, 'Active', if(last_order_date>=Passive_date,'Passive'))) as Status;