To Assign Value based on Top Value

Question

Hi,

I have 2 data sets in the following column format.

Search TextLength(Length of Search Text)reference1reference2Test1237123456Test45678910789888888

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 KeySearch Textreference1reference2TESTTest123,Test456789789789888888

Thanks in advance!

i_LKarthick · Answer

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 searchedTest 12345,indiaIndia12345

Table2:

Lookup Keywordreference1reference2Test india,123test1test12Indiatest2test22India Testtest3test32

Output:

Keyword to be searchedreference1reference2Test 12345,indiatest1test12Indiatest2test2212345test1test12

Regards,

LK

oly · Answer

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

* 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.
* Cross Tab - will concatenate all rows or select the first one
* 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

Assign Value based on Top Value.yxmd

Qiu · Answer

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