Match data

[Nandy]

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
Field1	Field2
time_huge_data	xxx
time_high_data	yyy
time_huge_direct	zzz

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.

Field1	Field1	Field2
this_AAA_data_is_direct_and_time_huge	time_huge_direct	zzz
this_AAA_data_is_huge_and_time_effective	time_huge_data	xxx
this_data_is_high_and_time_effective	time_high_data	yyy

[BrandonB]

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

 

[atcodedog05]

Hi @Nandy 

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

Join tool configuration.

Output:

Workflow:

Rename the columns in Join tool

Hope this helps 🙂

[T_Willins]

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.

[atcodedog05]

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

[Nandy]

Hi BrandonB,

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

Thanks,
Nandy

[Nandy]

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]

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]

Hi @Nandy 

Here is an approach.

Output:

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:

Is there any definitive logic on matching.

Hope this helps 🙂

[T_Willins]

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.