Search for a string within a field and return another field as a reference.

[9917mark]

Morning, the name is Mark

Could I ask for assistance on the following challenge please.

I am trying to map a business product file to a supplier's product file.

My product description contains reference to the suppliers product code within my description. I need to retrieve my corresponding product code.

Below is my supplier product file :

Below is my product file :

What I need to do is search for the Supplier Code (As on invoice) in the supplier product file, within New Description in my product file.

In other words I need PARTNO 1007860 to be returned when i search for TC322234 within my New Description

This information needs to be captured in a field on my product file.

Your help in this matter is appreciated,

Regards,

Mark

[swuehl]

You want to do this in the front end, by manually searching?

If yes, you can probably use the associative search. Enter the search box in a list box for field PARTNO, click on the two arrows on the right and type TC322234.

It should show matching values in field New Description. Selecting them should actually select your PARTNO.

Or do you want a script solution? If yes, please post some sample INLINE code (text) or a small sample QVW, it's quite hard to work on screenshots...

[9917mark]

Hi swuehl,

Thanks for the prompt response.

I would like a script solution please, as there could be up to 5000 lines to be matched.

I am attaching a QVW. !

[9917mark]

Sorry very new to this. How do I attach the file

[awhitfield]

Hi Mark ,

click on advanced Editor - top right of this window, then click attach, browse to your file etc.

Andy

[sasiparupudi1]

Please try the following script. It is assumed that the supplier code is always appended to the end of the new description

Data:

Load * Inline

[

Barcode Outer, Barcode Inner,Supplier Code, Inv Supplier Code Prod,Item Description

,x,TC322234,TC322234,ADAPTOR SDS MAX 400MM X M22 FOR ICT CORE BITS

,y,TC322237,TC322237,ADAPTOR SDS MAX 400MM X M22 FOR ICT CORE BITS

,z,TC322225,TC322225,ADAPTOR SDS MAX 400MM X M22 FOR ICT CORE BITS

,z,TC322228,TC322228,ADAPTOR SDS MAX 400MM X M22 FOR ICT CORE BITS

,x,SG ADA01,SG ADA01,ADAPTOR SDS MAX 400MM X M22 FOR ICT CORE BITS

];

Temp:

Load MaxString([Supplier Code]) as x Resident Data;

//get the max string of the supplier code

let vMaxLength=len(Peek('x'));

Drop Table Temp;

NoConcatenate

Product:

Load * ,

//SubField([New Description],' ',SubStringCount([New Description],' ')+1) as [Supplier Code1]

trim(mid([New Description],len([New Description])-$(vMaxLength),$(vMaxLength)+1)) as [Supplier Code]

Inline

[

PARTNO,STATUS,New Description

1007860,A,Adamptor SDS Max M22 X 400mm For TCT Core Bits Tork Craft TC322234

1007861,A,Adamptor SDS Max M22 X 400mm For TCT Core Bits Tork Craft TC322237

1007862,A,Adamptor SDS Max M22 X 400mm For TCT Core Bits Tork Craft TC322225

1007863,A,Adamptor SDS Max M22 X 400mm For TCT Core Bits Tork Craft TC322228

1007864,D,Air Brush SG ADA01

];

[9917mark]

awhitfield,

Thanks for the help.

swuehl‌ I have loaded a copy of the script for you.

Thanks,

Mark

[9917mark]

Thanks for the response. Unfortunately the supplier code is not always the last part of the description.

[sasiparupudi1]

May be this post can help you

https://community.qlik.com/message/782553#782553

[swuehl · Accepted solution]

Mark,

if you want a script based solution, it would make things easier if you post a sample file that can be reloaded (e.g. by also supplying demo input files or only using INLINE LOADs). But I can live with that for now.

I haven't found a match with the sample code TC322234, but with these codes:

PARTNO	FULLDESC	[Supplier Code (As on invoice)]	Match
1008262	Disc Cutting Masonry 115 x 3.0 x 22.22mm Tork Craft ABR115M-3	ABR115M-3	1
1008261	Disc Cutting Masonry 115 x 2.5 x 22.22mm Tork Craft ABR115M-4	ABR115M-4	1
1008260	Disc Cutting Masonry 115 x 2.0 x 22.22mm Tork Craft ABR115M-5	ABR115M-5	1
1008273	Disc Cutting Steel & S|S 115 x 1.6 x 22.2mm Tork Craft ABR115S-1	ABR115S-1	1
1008272	Disc Cutting Steel & S|S 115 x 1.0 x 22.2mm Tork Craft ABR115S-2	ABR115S-2	1
1008271	Disc Cutting Steel & S|S 115 x 0.8 x 22.2mm Tork Craft ABR115S-3	ABR115S-3	1
1008274	Disc Cutting Steel & S|S 115 x 3.0 x 22.2mm Tork Craft ABR115S-4	ABR115S-4	1
1008279	Disc Cutting Steel & SS 115 x 0.8 x 22.2mm Tork Craft ABR115S-5	ABR115S-5	1