topic Script to define customer as new or old in QlikView

Script to define customer as new or old

pauledrich — Mon, 30 Sep 2013 16:11:35 GMT

I have a problem I'm struggling to overcome, I have created a dashboard with around 10 tables all joined with no problem, the tables have customer information, transaction details, product descriptions and so on.

I want to give each customer a flag of "new" when they buy the first one of a range of products and use that date as the 1st first purchase and potentially a Flag of "Old" for future purchases.

The customer table has a created date, UniqueID.

The Transaction table is for a specific range of products which a customer may or may not have had.

I would like to have this calculated in the load script and have in the past been successful with a simple if - and Im quite green with scripting so any help or a steer on where to try and find out the answer would be of great help.

Re: Script to define customer as new or old

MK_QSL — Mon, 30 Sep 2013 16:53:04 GMT

Could you please load your script to make it easy for everyone to understand?

Re: Script to define customer as new or old

Anonymous — Mon, 30 Sep 2013 18:24:20 GMT

Paul,

I'm assuming that the same customer is both "new" and "old", depending on time of purchase. So, it makes sense to flag the purchase itself as "new" or "old" per customer per product. The "Yes" value is assigned when a customer buys a product for the first time. Example:
"Data" table includes CustomerId, ProductId, PurchaseId, and Date.
The script to could be:

tmp:
LOAD DISTINCT
CustomerId,
ProductId,
min(Date) as FirstPurchaseDate // this is the date of the 1st purchase of Product by Customer
RESIDENT Data
GROUP BY CustomerId, ProductId;

LEFT JOIN (Data) LOAD DISTINCT
CustomerId,
ProductId,
FirstPurchaseDate
RESIDENT tmp;
DROP TABLE tmp;

LEFT JOIN (Data) LOAD DISTINCT
PurchaseId,
CustomerId,
ProductId,
Date
if(FirstPurchaseDate=Date, 'Yes','No') as NewCustomer // this is your flag
RESIDENT tmp;
DROP FIELD FirstPurchaseDate;

Regards,

Michael

Re: Script to define customer as new or old

pauledrich — Mon, 07 Oct 2013 11:02:35 GMT

Hi Michael

Thank you for the reply & sorry for the late reply.

Looks good and thanks for the help - I will be getting back to this piece of work later this week and will keep you updated.

Thanks again.

Re: Script to define customer as new or old

pauledrich — Mon, 14 Oct 2013 13:54:03 GMT

Hi Michael

I have added the solution to the load script with amendments to correct field names and receive error messages in order :-

1. Table not found

tmp:

LOAD DISTINCT

CustomerID,

ProductID,

min(AgreementDate) as FirstPurchaseDate

RESIDENT Data

GROUP BY CustomerID, ProductID

Table not found

LEFT JOIN (Data) LOAD DISTINCT

CustomerID,

ProductID,

FirstPurchaseDate

RESIDENT tmp

Table not found

DROP TABLES statement

Table not found
LEFT JOIN (Data) LOAD DISTINCT
AgreementID,
CustomerID,
ProductID,
AgreementDate,
if(FirstPurchaseDate=AgreementDate, 'Yes','No') as NewCustomer

RESIDENT tmp

Field not found

Did not find the field "FirstPurchaseDate" from the DROP FIELD statement

Re: Script to define customer as new or old

Anonymous — Mon, 14 Oct 2013 14:26:13 GMT

Hi Paul,

The first error message means that there is no table "Data". You need to use the real name of the table. Notice that in my example it is not only in LOAD RESIDENT, but also in JOIN (Data) LOAD.

All other errors are the results of the first one.

Regards,

Michael

Re: Script to define customer as new or old

pauledrich — Fri, 18 Oct 2013 09:06:12 GMT

Hi Michael

I have made the change and included the table name where there is "Data" and still receive the same error messages?

Any ideas?

Re: Script to define customer as new or old

Anonymous — Fri, 18 Oct 2013 15:18:08 GMT

Can you send the log file or the qvw itself? Or the script as text?