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 AClient IDPurchase amountPurchase date1005001/1/20231006002/1/20231003003/1/20231002004/1/20231001505/1/2023 Dataset BClient IDEvent 1002/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

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

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

Developer Tools

Tips and Tricks

Help

Join

Accepted answers

cpet13

@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

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.

Screenshot 2024-05-22 123155.png

alteryxokan.yxmd

Raj

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

All comments

cpet13

AGilbert

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.

Screenshot 2024-05-22 123155.png

alteryxokan.yxmd

terry10

@alteryxokan

Append & filter (see attached).

event filter.yxmd

Raj

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!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15880

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703