Search possible match in given order

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
&hellip;&hellip;.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

Dynamic Processing

Accepted answers

MinaGO3

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 🙂

Create Mapping Table with Generate Rows.yxmd

All comments

MinaGO3

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 🙂

Create Mapping Table with Generate Rows.yxmd

Mukeshy12390

awesome! I like this tool "Generate Row".

I will check and let you know.

Thanks for the reply.

Mukeshy12390

It works as expected.

Quick Links

This months top contributors

LanisC 335

hhaigh1 280

erich_shiino 280

FláviaB 262

JORGE4900 235