topic Function like "contain" in the script for multiple substrings in QlikView

Function like "contain" in the script for multiple substrings

Tue, 10 May 2011 17:24:55 GMT

Hi!

Does any body have a example or idea for...

Basically I need to find not a single substring, but search among a list with multiples substrings…

So that when I would be running script for TABLE 2 I would like to find the codes listed in TABLE 1 in the field called text.

The codes into de field called text does not follow any pattern.... such as between ( ), " " etc...That's why I need to look individually for all the codes into de field text.

TABLE 1:

Code

CAD12

DDD25

B6D60

CDD12

4DD40

TABLE 2:

User Text

Alex Random text /CDD12/ random text

Rudolf Another random text *CAD12* random text

Paul Other text (DDD25) text

John Text 4DD40 random text

The result expected

User Text Code

Alex Random text /CDD12/ random text CDD12

Rudolf Another random text *CAD12* random text CAD12

Paul Other text (DDD25) text DDD25

John Text 4DD40 random text 4DD40

I thought about these functions (FindOneOf or substringcount ) but I not so sure..

Table:

LOAD

[user],

FindOneOf([Text],[Code],0)

substringcount([Text],

Any ideas or examples?

THANKS!

Function like "contain" in the script for multiple substrings

Tue, 10 May 2011 20:07:59 GMT

Give this a shot....

mid(Text,index(Text,'CDD'),5) as 'Code'

That's only gonna work if all of the codes are CDD## which the 5 you posted are so maybe that will be fine.

Function like "contain" in the script for multiple substrings

Tue, 10 May 2011 20:18:46 GMT

I really appreciate your help, but the Code table does not have a pattern also..

Code

CDD12

CDD25

CDD60

CDD12

CDD40

So the code does not always begin with CDD nor necessarily have 5 digits.

Do you have any other idea?

Someone else has?

Thanks

Function like "contain" in the script for multiple substrings

Tue, 10 May 2011 20:21:47 GMT

Well in the example you just posted again all of the codes start with CDD and have 2 numbers so... show me one that is different b/c I think one of us is misunderstanding and I'm not sure who that is yet.

Function like "contain" in the script for multiple substrings

Tue, 10 May 2011 20:29:59 GMT

I'm really sorry for the example given.

I have just edited the original topic

I think now it would be easier to understand what I meant wiht Code table does not have a pattern also..

Thanks!

Function like "contain" in the script for multiple substrings

Tue, 10 May 2011 20:37:57 GMT

Yeah that makes it a lot harder...

I have to figure there is some way to do this with peek and a for loop but that could be a nightmare if you have a LOT of data. Maybe try something like this...

For i = 1 to NoOfRows('Table1')

LET vCode=peek('Table1', 1-$(i),'Code');

LOAD

User,

Text,

$(vCode) as 'Code'

INLINE [

inline stuff goes here

]

WHERE index(Text,$(vCode))>0;

Next i

Function like "contain" in the script for multiple substrings

chriscammers — Wed, 11 May 2011 01:28:05 GMT

I think your best bet take a couple of steps, what you'll want to do is load all the strings into a single variable and then use the wildmatch() function to figure out what string has the match. So I'll try to modify your example to illustrate.

//Load the codes into your table adding a record number and asterics on the code so you can use the

//wildmatch function later

TABLE1:

Load * Inline [

Record, Code

1, *CAD12*

2, *DDD25*

3, *B6D60*

4, *CDD12*

5, *4DD40*

];

//Now in a temporary table you can concatenate the codes enclosed in single quotes and delimited with //commas

CodesTemp:

Load

Concat(chr(39) & Code & chr(39), ',') as Codes

Resident TABLE1;

//Store the concatenated code list into a variable

Let vCodeList = Peek('Codes',0,'CodesTemp');

//The wildmatch function returns a number indicating the position of the matching list item

TABLE2:

Load

User,

Text,

WildMatch(Text,$(vCodeList)) as Record;

Load * Inline [

User, Text

Alex, Random text /CDD12/ random text

Rudolf, Another random text *CAD12* random text

Paul, Other text (DDD25) text

John, Text 4DD40 random text

];

//Finally join the table and clean up.

Left Join (TABLE2)

Load * Resident TABLE1;

//Clean up

Drop Tables TABLE1, CodesTemp;