Dynamic Replace

[CSmith16]

Hello,

So I have a dilemma, we have transaction data getting pulled into alteryx and there is a need to update a column within the workflow based off set criteria.

We have transactions that come in with gratuity assigned to them, so we have a few records for the same transaction but with differing amount, source_account, and tender_type columns. They share the same transaction_reference however. I need a way to turn the original output:

(filtered to only show relevant transaction)

Into this Desired Output:

This needs to be done dynamically without interfering with other transactions, especially those that have the same source_account of "Food".

What I am trying to do is replace the tender_type column for records with a source_account of "Food", and share the same transaction reference, with what is in the source_account for the actual total from the transaction (source_account prefixed with "HC "). 

[LindonB]

Hi there,

It sounds like you're describing doing a join on transaction_reference = source_account after filtering the left table for source_amount = "Food" and the right table for left(source_amount,2)="HC". 

Unfortunately, the first image (Original Output) is too small to make out. Would you mind reposting it, and I'll be happy to share a solution?

[CSmith16]

Hey thanks for your response @LindonB! I actually just also included the screenshots as the original excel file attached to the post. But I'll provide them here as well:

(If they are too small, I apologize, I have an awfully scaled monitor, it is too big and this is a common occurrence)

Original:

Desired:

The data on that excel sheet is also only the specific transactions that need this, there are actually more than 8,000 records but I am just working with the 8 now to get this to work. And not all records with the source_account of "Food" are going to need this updated tender_type just the ones that have an Open Grat assigned to the transaction_reference.

[binuacs]

@CSmith16 if you want to update the tender_type field use multi-row tool

[CSmith16]

@binuacs 

Problem with that is what do I do with all the rest of the data? It will replace every null tender_type with what is the row above. I can include the actual dataset with all 8k transactions if needed, just not sure if I'd need to change stuff around for company privacy considering this is financial data. 

[CSmith16]

@binuacs @LindonB 

I am attaching a packaged workflow containing three files, all of the data, just the data that this would be affecting, as well as the indented output.

[CSmith16 · Accepted solution]

Got my fix if anyone sees this.

[LindonB]

Excellent. Glad you were able to get it to work!