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.
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;