topic Re: Search for words in a string in QlikView

Search for words in a string

scott_brown — Wed, 30 Aug 2017 17:05:07 GMT

I have struggling to perform a load where there may be duplicate 'StyleNumbers' that are indeed seperate items. I am wondering if i can somehow match at least one word in the 'Description' to qualify along with joining on StyleNumber.

Example:

Supplier1:

StyleNumber, ItemDescription1

6615, Nice Shiny Object

6616, Small Widget Piece

6617, Plastic and Rubber

From sourceA;

Supplier2:

StyleNumber, ItemDescription2, UPC

6615, Object Shiny Nice, 123

6616, Large Random Thing, 456

6617, Rubber Plastic, 789

From sourceB;

Drop Field ItemDescription2

Re: Search for words in a string

its_anandrjs — Wed, 30 Aug 2017 17:11:59 GMT

What is your expected output let me know.

Re: Search for words in a string

scott_brown — Wed, 30 Aug 2017 17:24:33 GMT

Im looking to only return a 'UPC' if the 'StyleNumber' and at least 1 word out of the ItemDescription fields match.

StyleNumber, ItemDescription1, UPC

6615, Nice Shiny Object, 123

6616, Small Widget Piece, NULL

6617, Plastic and Rubber, 789

Re: Search for words in a string

its_anandrjs — Wed, 30 Aug 2017 17:32:43 GMT

Sorry i still not understand can you provide some more example.

Re: Search for words in a string

scott_brown — Wed, 30 Aug 2017 18:14:09 GMT

Example:

Supplier1:

Load

StyleNumber, ItemDescription1

6615, Nice Shiny Object

6616, Small Widget Piece

6617, Plastic and Rubber

From sourceA;

Join

Supplier2:

Load

StyleNumber, ItemDescription2, UPC

6615, Object Shiny Nice, 123

6616, Large Random Thing, 456

6617, Rubber Plastic, 789

From sourceB;

I only want to join if StyleNumber matches and one word from ItemDescription matches, 6615 and 6617 would return a UPC, and 6616 would not because no words match from ItemDescription.

Expected Outcome:

StyleNumber, ItemDescription, UPC

6615, Nice Shiny Object, 123

6616, Small Widget Piece, NULL

6617, Plastic and Rubber, 789

Re: Search for words in a string

antoniotiman — Wed, 30 Aug 2017 18:21:34 GMT

HI Scott,

maybe like this

Temp:
LOAD *,SubField(ItemDescription1,' ') as LinkField Inline [
StyleNumber, ItemDescription1
6615, Nice Shiny Object
6616, Small Widget Piece
6617, Plastic and Rubber];
Left Join
LOAD StyleNumber,UPC,SubField(ItemDescription2,' ') as LinkField Inline [
StyleNumber,ItemDescription2, UPC
6615, Object Shiny Nice, 123
6616, Large Random Thing, 456
6617, Rubber Plastic, 789];
Table:
// LOAD StyleNumber,ItemDescription1,If(Count >=2,UPC,Null()) as UPC; // Optional according Your preferences
LOAD StyleNumber,Only(ItemDescription1) as ItemDescription1,Only(UPC) as UPC,Count(UPC) as Count
Resident Temp
Group By StyleNumber;
Drop Table Temp;

Regards,

Antonio