topic Categorize customers dependent on selected Date in QlikView

Categorize customers dependent on selected Date

Mon, 15 Jun 2009 14:08:48 GMT

Hi *,

I'm trying to categorize my customers into "known-customers" and "new-customers" dependent on the selected order date.
E.g.
The Customer 3 (Elvis P.) was created on 03.03.2009. He ordered 07.07.2008 and 01.04.2009.
If I select the year 2009 he has one order and is a known-customers because there was an order in 2008.
If I select the year 2008 he is a "new customer" because his first order was in 2008.
Is there a way to implement this?

Thanks.
Regards

Aloah

Categorize customers dependent on selected Date

Mon, 15 Jun 2009 15:41:24 GMT

Aloah,

Try this after you have loaded the Orders table in your example:

Load *,
if (previous(Customer_ID) = Customer_ID, 'Existing', 'New') as Customer_Type
resident Orders
order by Customer_ID, Order_Date;

This will create a new table and the original 'Orders' can be dropped.

Regards,

Gordon

Categorize customers dependent on selected Date

Mon, 15 Jun 2009 15:52:56 GMT

Thanks Gordon. Works perfect.

Categorize customers dependent on selected Date

johnw — Tue, 16 Jun 2009 02:49:36 GMT

And as a side comment, I believe it is faster and uses less memory to just join the table back to itself rather than loading a new table and dropping the first, i.e.,

LEFT JOIN (Orders)
LOAD
 Order_ID
,if(previous(Customer_ID)=Customer_ID,'Existing','New') as Customer_Type
RESIDENT Orders
ORDER BY Customer_ID, Order_Date
;

Also, you'll need to use max(Customer_Type) when there are multiple orders in the year, as only the first order will have a customer type of New.