Find Replace Help

[lacymills2]

I have searched and not found the best way to handle this. I have 2 sets of data that I need to join together and seem to be working with find/replace tool, but not quite working with a scenario as below. All made up but occurs frequently. 

Table one has my usage information and is by day, but I summarize to the month. In this example assume asset ID is a customer within same organization so pretend 1-15 they use payment X then 16-31 they use payment Z. Also Split 1 and after are the formulas I use.

Asset ID	Usage Reference	Usage QTY	Month Start	Split 1	Split 2	Order ID 1	Order ID 2	Order IDs
0b6c8dae-0584-3f03-dee3-ee17ca17e179	[_6UCHSZo6IsaEkcE-ZYIRoTpISw-swbx1]:[0981D33F89154D77E01D3BB7182C4ED2D961409A359984940C14B4B8A8D3A7AE],[KjZ9Ymvi_92Zi1NSAdBYS5xMXpC2lKur1]:[0981D33F89154D77E01D3BB7182C4ED2D961409A359984940C14B4B8A8D3A7AE]	10000000	2020-10-01	[_6UCHSZo6IsaEkcE-ZYIRoTpISw-swbx1]:[0981D33F89154D77E01D3BB7182C4ED2D961409A359984940C14B4B8A8D3A7AE]	[KjZ9Ymvi_92Zi1NSAdBYS5xMXpC2lKur1]:[0981D33F89154D77E01D3BB7182C4ED2D961409A359984940C14B4B8A8D3A7AE]	[_6UCHSZo6IsaEkcE-ZYIRoTpISw-swbx1]	[KjZ9Ymvi_92Zi1NSAdBYS5xMXpC2lKur1]	[_6UCHSZo6IsaEkcE-ZYIRoTpISw-swbx1]-[KjZ9Ymvi_92Zi1NSAdBYS5xMXpC2lKur1]
0bdd3b9d-54d3-fb9b-aa18-fbe7f44ed3d5	[_6UCHSZo6IsaEkcE-ZYIRoTpISw-swbx1]:[0981D33F89154D77E01D3BB7182C4ED2D961409A359984940C14B4B8A8D3A7AE],[KjZ9Ymvi_92Zi1NSAdBYS5xMXpC2lKur1]:[0981D33F89154D77E01D3BB7182C4ED2D961409A359984940C14B4B8A8D3A7AE]	500000	2020-10-01	[_6UCHSZo6IsaEkcE-ZYIRoTpISw-swbx1]:[0981D33F89154D77E01D3BB7182C4ED2D961409A359984940C14B4B8A8D3A7AE]	[KjZ9Ymvi_92Zi1NSAdBYS5xMXpC2lKur1]:[0981D33F89154D77E01D3BB7182C4ED2D961409A359984940C14B4B8A8D3A7AE]	[_6UCHSZo6IsaEkcE-ZYIRoTpISw-swbx1]	[KjZ9Ymvi_92Zi1NSAdBYS5xMXpC2lKur1]	[_6UCHSZo6IsaEkcE-ZYIRoTpISw-swbx1]-[KjZ9Ymvi_92Zi1NSAdBYS5xMXpC2lKur1]

table 2 is my payment detail

Payment Amt	Payment ID	Order ID	Payment Date
293.05	6805051319	KjZ9Ymvi_92Zi1NSAdBYS5xMXpC2lKur1	2021-06-01

Using find replace and append this is the output I get, which is not the correct Payment Amt. I am finding Order ID within the Order IDs field that I recreated - I had to do this bc I found that searching within Usage Reference field didn't work and the field was also getting truncated no matter how I ended up changing settings.

Payment Amt	Payment ID	Order ID	Payment Date	Asset ID	Usage QTY	Month Start
293.05	6805051319	KjZ9Ymvi_92Zi1NSAdBYS5xMXpC2lKur1	2021-06-01	0b6c8dae-0584-3f03-dee3-ee17ca17e179	10000000	2020-10-01
293.05	6805051319	[KjZ9Ymvi_92Zi1NSAdBYS5xMXpC2lKur1]	2021-06-01	0bdd3b9d-54d3-fb9b-aa18-fbe7f44ed3d5	500000	2020-10-01

[mbarone]

So are you ultimately wanting to join the two files based on a common key?  It looks like the common key would be Order ID which is plain table 2, and contained within "Usage Reference" in table 1.

Is this correct?

[lacymills2]

yes thats correct. i found making an index then cross tab the data then joining it together helped. Still struggling with if its the best way to go about it.

[mbarone]

Okay (trying to make the process as simple and efficient as possible).  It appears that the Usage Reference in table 1 is broken into 4 sections, each individual section in their own set of brackets ( "[" "]").

So that the sets of brackets appear as follows:

[_6UCHSZo6IsaEkcE-ZYIRoTpISw-swbx1]

[0981D33F89154D77E01D3BB7182C4ED2D961409A359984940C14B4B8A8D3A7AE]

[KjZ9Ymvi_92Zi1NSAdBYS5xMXpC2lKur1]

[0981D33F89154D77E01D3BB7182C4ED2D961409A359984940C14B4B8A8D3A7AE]

And it looks like the key for joining is that THIRD set of brackets.  Is that ALWAYS the case?  

[mbarone]

If so, you can use a RegEx tool in Parse mode to split out that 3rd section of text in the brackets.

The Regular Expression here is:

\[.*?\].*\[.*?\].*(\[.*?\])

And the formula is:

Replace(Replace([KeyJoiner],'[',''),']','')

[lacymills2]

So in above would join [_6UCHSZo6IsaEkcE-ZYIRoTpISw-swbx1] &  [KjZ9Ymvi_92Zi1NSAdBYS5xMXpC2lKur1] then search from the payment side? Does that make sense? So I split that usage reference then join it back together to make it a search on that 1 field.

[mbarone · Accepted solution]

I'm not doing a find/replace here.  I'm trying to figure out what the common key is, and where it's located in the first file.  You had said it's that 3rd bracketed string in the first file, and the order id in the 2nd file.  IF that's the case, and IF it's always the 3rd bracketed string in the first file, then you have to FIRST isolate just that string, and then join it to the other file on that string = order id.

So two steps.  First isolate the common key.  Second join the files together on that common key.

Here's some Tool Mastery Index References on the two tools I'm using:

RegEx Tool 

Join Tool 

[lacymills2]

Thank you, think that having it originally set up in excel limited how I was trying to solve. 

[mbarone]

Welcome!  And yep - that's the great thing about Alteryx - it gets you thinking more about the data and logical process itself :)