Alteryx Joins

[AhanaR]

Hi All,

I'm very new to alteryx and have been trying to build a workflow connecting 2 excel files.

I would want to look up data from 2 files and combine it with the master data. could someone tell me how that's done in alteryx?

This is how the fields in my master file look :

This is my 2nd file :

The first string value in the doct_ID_Item field from the master file without leading zeros is identical to the document ID field in the second file. In excel, I write a lookup formula to get the below-desired output. how can I do this in Alteryx?

Desired Output is :

[ShankerV · Accepted solution]

Hi @AhanaR 

One way of doing this.

Note: Please input the dataset replacing the text input tool for the expected output.

[apathetichell]

I'd use record id (for a primary to key), Text to columns and split on | (to rows) , and then make sure I got rid of trailing/leading spaces (datacleanse can do this) - then I'd join.

@ShankerV has another way - which is awesome, but might be a bit harder to adjust/replicate as needed. 

[RobertOdera]

Hi, @AhanaR 

Kindly consider providing sample tables we can copy-paste, excel, or text files.

Otherwise, the community has to re-create your samples before offering how-to solutions for you to consider - Cheers!

In the meantime:

-For table 1: use the parse out doct_ID_Item on the delimiter = pipe | into two columns A, B (leave extra in the last column)

-Using the Join tool, Inner Join table 1 to table 2 on table 1 A == table 2 document ID, deselect duplicates in the join tool options

-Looks like you are only interested in the results out of the J node of the join tool

Cheers!

[AhanaR]

Hi Shekar,

Thank You so much. This solution worked for me.

I replicated your workflow to mine but could you help me understand the purpose of giving (\d+) in regular exp. and also does size = 14 in output col define the string size up to 14?

[AhanaR]

Sure Robert, I will make sure i post data that can be copied not a snippet :) 

[AhanaR]

Thank You 

[ShankerV]

Hi @AhanaR 

The purpose of using (\d+) is making the regex tool to read only the first digit and + denotes n number of digits followed by it.

When it encounters any value other than digit like space or pipe symbol, it will stop reading.

Yes, 14 denotes the string size.

Hope it helps!!!!!

[AhanaR]

Thank You so Much