Capture details from the earliest purchase record, for each customer

[#1]

I'm sure this question has been asked a million times before, but it seems that finding the correct search terms is nigh impossible.

The setup is simple:

Two tables. Customers, and Purchases. The association is pretty obvious.

What I want to do is in the load script, identify any given member's FIRST purchase, and capture information about it either in a summary table (preferred), or simply flag them somehow so I can use set analysis expressions to get at them.

Presume that the only fields available on the purchase are:

Customer ID

Purchase ID

Purchase Amount

Purchase Date

I've built a summary table that is capturing most of the information I need, but I want the AMOUNT of the FIRST purchase for each member.

Thanks for your help!

[johnw · Accepted solution]

I'd prefer flagging to a summary table, though it's simple enough to just load the flagged records to another table if you want once the records are flagged (though there is probably an easier way).

LEFT JOIN (Purchase)
LOAD
"Purchase ID"
,if("Customer ID"<>previous("Customer ID"),-1) as "First Purchase"
RESIDENT (Purchase)
ORDER BY "Customer ID", "Purchase Date"

This isn't actually sufficient if they can purchase more than one time in a day, but I assume the real world example will have timestamps instead of just dates.

[juleshartley]

Hi Jonh - I know this is an old post but... I've got a situation where I need to do the above BUT I have exactly the problem you highlighted, where there is no timestamp but there could be intra-day records. I've been asked (rather bizarrely) to arbitrarily flag one as a duplicate. Any ideas?!

It makes the left join a bit difficult...

[juleshartley]

Think I've answered my own question - I guess I need to add an additional field to the join key with a max() or something... just trying to get it working.