Search possible match in given order

[Mukeshy12390]

Hi Everyone - 	

Is there anyway we can achieve below solution.. 	
here is the situation.. I have two file  "CompanyDetails_InputFile" and "Mapping".	
	
CompanyDetail_Input file has   ---- two column 1)Company_code  2)Sort_Code 	
Company code would be 5 digit  	
Sort_Code  could be between 3 to 20 Char 	
	
And 	

Mapping File has --- 3 column 1)Company_code  2)Sort_Code  3 )Cost Center	
all I wanted to do look up in two way...	
	
	
	
1st lookup is exact match  of Company code and Sort_Code which is pretty easy :)	

	
for those record we don't get any match ....we have do follow below logic.	
for example we don't get any match for below record .	

Company_code	Sort Code
1001	Alteryx Community

I will be looking for  below combination in given order 	
	
Company_code	Sort Code
1001	Alteryx Community
1001	Alteryx Communit
1001	Alteryx Communi
1001	Alteryx Commun
1001	Alteryx Commu
1001	Alteryx Comm
1001	Alteryx Com
1001	Alteryx Co
1001	Alteryx C
…….So on until last chart for sort code	
	
company code would remain same but sort_code keeps decreasing by one Char each time.

I hope this makes sense.

any help would be appreciated.

Thanks
Mukesh Y
	

[MinaGO3 · Accepted solution]

Hi @Mukeshy12390,
There are probably a lot of ways to do this, one would be to generate rows of all truncations of the string to join onto like in this example below:

Let me know if this helps 🙂

[Mukeshy12390]

awesome! I like this tool "Generate Row".

I will check and let you know.

Thanks for the reply.

[Mukeshy12390]

It works as expected.