Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Match data

Nandy
8 - Asteroid

Hello,

 

I have a dataset that contains Input 1 & Input 2 as below,

Input 1
Field1
this_AAA_data_is_direct_and_time_huge
this_AAA_data_is_huge_and_time_effective
this_data_is_high_and_time_effective

 

Input 2
Field1Field2
time_huge_dataxxx
time_high_datayyy
time_huge_directzzz

 

Need to compare these & map ,to get the output as below . The positions of the string vary , if input 1 contains input 2 , i need field2 against it.

Field1Field1Field2
this_AAA_data_is_direct_and_time_hugetime_huge_directzzz
this_AAA_data_is_huge_and_time_effectivetime_huge_dataxxx
this_data_is_high_and_time_effectivetime_high_datayyy
10 REPLIES 10
BrandonB
Alteryx
Alteryx

Can you use a Join tool and "Join by Record Position"?

 

record position.png

atcodedog05
22 - Nova
22 - Nova

Hi @Nandy 

 

You acheive it by setting join by record position in Join tool

 

Join tool configuration.

atcodedog05_0-1603208456885.png

 

Output:

atcodedog05_1-1603208505204.png

Workflow:

atcodedog05_0-1603208598070.png

 

Rename the columns in Join tool

 

Hope this helps 🙂

T_Willins
14 - Magnetar
14 - Magnetar

Hi @Nandy,

 

When I read you issue, it looks like it is more complex than just joining by record position.  What you need to create to join the data is a common key.  Attached is a workflow that uses a series of RegEx parses to match keys, then concatenates them together.  Since "data" is present in your "time_huge_direct" data, I used a formula tool to remove "_data" from the Input 1 key if "direct" is in the key as well.

 

Match data.jpg

 

atcodedog05
22 - Nova
22 - Nova

@T_Willins you are right seems like we missed out on a great pattern here.

Nandy
8 - Asteroid

Hi BrandonB,

 

I cant use a Join here , the record id are not identical.

Thanks,
Nandy

Nandy
8 - Asteroid

hi,

 

The records wont match , i am trying to match input 2 with input 1 .

 

Input 2 will vary each time , and it should be the  master to look into Input 1.

 

Thanks,

Nandy

Nandy
8 - Asteroid

Hi Willins,

 

Thanks for the idea, but the words used such as " direct", "huge" ,"high" are not always the same, the input is dynamic changing.

 

All i need is match the data of input 2 with input 1, the positions of the word vary each time.

 

Thanks,

Nandy

atcodedog05
22 - Nova
22 - Nova

Hi @Nandy 

 

Here is an approach.

 

Output:

atcodedog05_0-1603257234188.png

It works by checking whether words in source is in target.

Logic words in Source_Field1 is in Field1

 

But the output is not same.

 

Workflow:

atcodedog05_1-1603257315827.png

 

Is there any definitive logic on matching.

 

Hope this helps 🙂

T_Willins
14 - Magnetar
14 - Magnetar

HI @Nandy,

 

I was thinking of a similar approach to @atcodedog05, but as shown if your data matches more than one Input 2 key, it will return multiple results.  You would need a tiebreaker such as first or last, which can be done through a Summarize tool.  Limitations of this approach are it is fixed to the number of keys from Input 2 and, if there are a considerable amount of records, the Append Fields tool may not work efficiently.  Both of these limitations could be solved with an iterative and/or batch macro, but it would take further investigation of your data to determine what would be the best approach.

Labels