Filtering by the appearance in rows

[ievpal]

Hi,

I am trying to filter only those customers, who first bought A product or first brought B product and secondly bought A product to see what else they have brought after buying  A.

This is a sample of data I have:

Required result should look like this:

Could this be done in Alteryx?

[jdunkerley79]

Just to make sure I understand.

For each customer, you want to keep the records of when they bought product A plus the purchase before and after product A.

If this is the case then yes do-able within Alteryx using a MutliRow formula. An expression like:

'A' IN ([Row-1:Product_ID], [Product_ID], [Row+1:Product_ID])

grouped by Customer_ID should do what you need.

Sample attached

[ievpal]

Hi,

Not exactly.

What I`am interested to see is:

If a customers first purchase was A product or he bought A product after firstly buying B product then I want to see his whole purchases history.

If a customer bought C product first I am not interested in him.

In my real data set I have 5 products, so if the customers first purchase was D or E I am not interested in those either.

[jdunkerley79]

OK. Have updated sample to do this.

First I add a customer purchase id which tracks the first purchase (or second if first = 'B') within a customer (Multi Row Formula, over 2 rows):

IIF(ISNULL([Row-1:CustomerPurchaseID]),
    [Product_ID],
    IIF(ISNULL([Row-2:CustomerPurchaseID]) AND [Row-1:CustomerPurchaseID] = 'B', [Product_ID], [Row-1:CustomerPurchaseID])
)

Then filter this where result is A 

Then pick the unique customer ids

Finally join back to input stream to just keep those customers

Updated sample attached

[ievpal]

The problem is it doesn`t give the right result...  :/

It still gives me the ab3 client, which first product was C. (This client should be ommited, because his first purchased product wasn`t A or B and the second A.) 

[jdunkerley79 · Accepted solution]

Odd definitely filters that on mine:

Have attached it again in case I attached wrong version!

[ievpal]

It works now. Thank you so much!!! :)