To Assign Value based on Top Value

[i_LKarthick]

Hi,

I have 2 data sets in the following column format.

Search Text	Length(Length of Search Text)	reference1	reference2
Test123	7	123	456
Test456789	10	789	888888

Expected Output:

Trying to Search String in one Column(Search Text Column), if string found then have to check for length, to fetch  reference1,reference2 column. Search Key have to be placed in output as follows,

Match Key	Search Text	reference1	reference2
TEST	Test123,Test456789	789	789888888

Thanks in advance!

[Qiu]

@i_LKarthick 
Do you have a larger data sample set? so we can observe te pattern of "match"？

[oly]

Hi @i_LKarthick ,

Your requirements are not very accurate, if you could validate how you identify the Match part - is that common length within all the rows or different rows should be grouped by different common root? Could you identify root part as text only before the digits?

For example:

ROW123456

ROW12345678 - Common Match would be ROW123456

TEXT1456789 

TEXT456789 - Common Match would be TEXT

Also, your Sample result is not very consistent with the desrciption that you put as "To Assign Value based on Top Value".

Overall there's 3 ways to work with merging rows

1. Summarize tool is allows to concatenate String Values or aggegate numeric. In your example I converted Int to String to be able to concatenate. You can define separator (default is comma) between concatenated values, or concatenate flat.
2. Cross Tab - will concatenate all rows or select the first one
3. Multi-Row Formula - utmost flexibility when you can calculate required values within groups of records like Matched Text.

Please see example attached and let me know how we can improve from here.

Best,

Oly

[i_LKarthick]

Hi Oly,

Thanks for your help. Now i have to search keyword from one table to other as fuzzy match upto 60% match. if it match then i have to pull relevant value to output table. 

Table1:

Keyword to be searched

Test 12345,india

India

12345

Table2:

Lookup Keyword	reference1	reference2
Test india,123	test1	test12
India	test2	test22
India Test	test3	test32

Output:

Keyword to be searched	reference1	reference2
Test 12345,india	test1	test12
India	test2	test22
12345	test1	test12

Regards,

LK