Filter based on a event that is recorded on a seperate file.

[alteryxokan]

Hi, 

I'm looking to identify clients who purchased from us after a specific time of the year. This date is different for each client and I need their transactions let's say after 27th of Feb 2023. Example dataset is below

Dataset A
Client ID	Purchase amount	Purchase date
100	500	1/1/2023
100	600	2/1/2023
100	300	3/1/2023
100	200	4/1/2023
100	150	5/1/2023

Dataset B
Client ID	Event
100	2/27/2023

How can I filter dataset A in a way it will only keep the transactions after event date in Dataset B. In this example I expect to see last 3 transactions. I unfortunately do not have any solution I'm developing yet. Brain just stopped.

Thank you

[cpet13 · Accepted solution]

@alteryxokan you can join the two files/datasets together into one, bring the date from dataset B into dataset A, then use a filter tool saying to only bring records where the purchase date is greater than the event date (which is now a field in dataset A).

[AGilbert · Accepted solution]

There are two easy ways to join the data; the Join or the Append tool. Use of one over the other depends on what your full datasets look like. 

Also, your date fields are a string datatype. See the expression in the filter tool below to convert the two values to a date datatype. 

[terry10]

@alteryxokan 

Append & filter (see attached).  

[Raj · Accepted solution]

@alteryxokan
you can join both your files with your "Client ID" 
then you can use filter tool to filter you condition

hope this helps

#append tool works good if your second file only have one record but in case you have multiple records in second file you will end up with false information as unnecessary records will be generated.

[alteryxokan]

Thanks everyone for replies in a short time!!! I'm trying to apply this to my dataset and having problem with the appending. Somehow it is only appending the first cell instead of matching with the client ID. I will revert back shortly! 

[alteryxokan]

I could not work with append fields but join worked! I appreciate the help!!!

[AGilbert]

The append tool works by adding each record on the 's' anchor to each record on the 't' anchor.

It's one of the few tools which can generate new records, so it requires some care when using. For example, if 10 records are on the 't' anchor and 5 records are on the 's' anchor the result will be 50 (10*5) records.

It's a useful tool in the right situation but there is no concept of matching on certain fields or otherwise limiting output. If your curious about it start up a new community question!