Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Function like "contain" in the script for multiple substrings

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!

6 Replies
Not applicable

Function like "contain" in the script for multiple substrings

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.

Not applicable

Function like "contain" in the script for multiple substrings

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

Not applicable

Function like "contain" in the script for multiple substrings

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.

Not applicable

Function like "contain" in the script for multiple substrings

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!

Not applicable

Function like "contain" in the script for multiple substrings

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

chriscammers
Contributor III

Function like "contain" in the script for multiple substrings

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;

Community Browser