Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

General Discussions has some can't miss conversations going on right now! From conversations about automation to sharing your favorite Alteryx memes, there's something for everyone. Make it part of your community routine!

Erase Transactions in which the items are further returned

Vincem35
5 - Atom

Dear Community., 

 

I have a list of transactions together with a unique client ID.

Some of my clients are returning the items a few days after having purchased them.

 

I would like to erase transactions for which items are returned - which process should i apply to my data?

 

Thanks in advance for your help!

 

Cheers

GiuseppeC
Alteryx
Alteryx

Hi @Vincem35,

 

You could use the Join tool to match the client ID and transaction between the 'returned item' data set and the 'all transactions' data set. The Data in the L or R output of the Join (depending on how you configured it) contains what didn't match, i.e. all transactions, minus the returned items.

 

Hope this helps!

 

Giuseppe

Vincem35
5 - Atom

Thanks Gisueppe for your answer

 

I thought about it but this issue is that i have a new transaction everytime i have a return - see example below

 

I would like to erase transaction 1 because of Transaction 3.

 

client Transaction #Amount
1117
1265
13

-17

 

Any thought?

 

Thanks again

klyuka
8 - Asteroid

1. When you are typing cancelation you/operator could write the ID of initial transaction in new column.

After that you can use left join with transactions with neggative ammount - take transactions that has no elimination.

2. How to guess which transatcion was canceled if yesterday I bought 10, today I bought 10, tomorrow I bring 10 back - what transaction to eliminate?

Is it normal to eliminate last with same ammount (LIFO method)?

For hard case we take +10, +10, +10, -10, -10 for one client (so that you need to match carefully).

Suppose it is enough for you then you need new column for filtering rows.

Easy way - suggest iterations - you for (each client + each sum) find earliest cancelation (transaction 4), than you find the latest transaction with such sum that is before cancelation.

Set two flags for filtering out. After that you check a base - how many negative left, and if still - make process again.

Keep all eliminated transactions in seperate base. You may need it later.

The workflow is attached - in the left output of join you get data with one elimination in pair (client+ammount), it will not eliminate pair if elimination transaction goes before sales transaction.

May be you need to run this workflow several times if you have clients with multiple similar cancelations.

 

GiuseppeC
Alteryx
Alteryx

Thanks, @Vincem35, this clarifies what you want to achieve.

 

I am assuming that you want to erase both transaction 1 and 3 in this case. Is this correct?

If so, please see if the attached workflow gives you what you need.

 

 

klyuka
8 - Asteroid

@GiuseppeC, I tested your solution it eliminates all purchuases with 17 ammounts. And this may lead to error if several similar orders from one client.

I suggest set (+10, +10, +10, -10, -10) for testing algorythm.

GiuseppeC
Alteryx
Alteryx

@klyuka, true! Was trying to show the principle of how to match.

The complete solution depends on what the actual data set @Vincem35 is working on looks like. There could be an item description field or other information (i.e. date of purchase, ...) that could simplify the matching, which means @Vincem35 just needs to change the fields to match on in the Join tool.

 

Hope this helps,

Giuseppe

Vincem35
5 - Atom

Thank you guys !\

Great solution!

JT01
5 - Atom

Hi Vincem35,

 

Did one that is able to remove exactly the number of refunded transaction by assigning IDs for transactions under the same client, with the same amount.

 

Only limitation is that it can only remove either the earliest or the most recent transactions.

 

Hope it helps. 

Labels