wildmatch and looping though table

[amien]

I have this data.

if want on each 'string' the correct teamcode. so output should be like this: AALS01DD, 10, *LS01*, team1

so for each record in table2 i want to do a wildmatch on each record in tabel1. and when its a hit then add the correct team.

i'v tried regex: http://community.qlik.com/forums/p/21027/80676.aspx#80676

this works .. but is very slow and i also want, if no hit, then put 'string' into a seperate team (team others)

i'v tried wildmatch: http://community.qlik.com/forums/p/23326/98327.aspx#98327

this is faster, but i dont know how to add the missing 'string' to a seperate team (team others) (the WHERE clause is an issue)

i could use a lot of nestled IFS during loading, but i have to check a table that contains about 50 records. So thats means 50 IFs ..

any ideas? thanks alot!

table1:
LOAD * INLINE [
 match, description
 *LS01*, team1
 *LS02*, team2
 *LS03*, team3
];

table2:
LOAD * INLINE [
 string, value
 AALS01DD, 10
 ABLS01EE, 20
 ACLS02GG, 50
 LLLS02EE, 10
 RRLS03QQ, 50
];

[amien]

sorry .. its possible that the matching values are on a different position. thats why i used the wildcards.

i really like the applymap.. i will see if i can load this two of three times for all the different positions.

or is it possible to use a wildcard in applymap?

[amien]

John ... your last solution is quit fast en flexible .. and working 🙂 .. gonna test some more

[johnw]

Nimish Shah wrote:You mean the following way

Yeah, almost. I was thinking this, which I suspect is slightly faster than using a preceeding load, though I've never done any performance testing to be certain:

table1:
mapping LOAD * INLINE [
match, description
LS01, team1
LS02, team2
LS03, team3
];

table2:
Load *,applymap('table1',mid(string,3,4),'others') as Desc
INLINE [
string, value
AALS01DD, 10
ABLS01EE, 20
ACLS02GG, 50
LLLS02EE, 10
RRLS03QQ, 50
TTLS05QQ, 10
];

But as we've now learned, it's not what Amien needs. Ah, well.

[johnw]

Amien wrote:John ... your last solution is quit fast en flexible .. and working 🙂 .. gonna test some more

OK, good. I was worried about the speed.

[#6]

Hi John,

Thanks for pointing this out.

Nimish

[rwunderlich]

Try the Qlikview Cookbook example "Mapping with a table using wildcards"

http://robwunderlich.com/Download.html

This is a variation of the generated pick(match()) shown earlier. I use this technique frequently and have always been happy with the performance and the setup is easy.

-Rob

[johnw]

Heh, looks like I reinvented your wheel, then. And here I thought I was being clever. 😉

[#9]

Rob,

Thiis is extraordinary!. Very very clean solution.

Nimish

[amien]

Thanks all .. i'v tested about scripts (John and Rob) .. Rob's one is cleaner and more flexibel, but slower (using 3mil records with 1000 strings to match). Working with John's solution at this moment, but still testing futher.

have some issues with matching if i only have 1 word to match and the position of that word is all at the end of the script.

"blablabla test" --> "test" .. scripts puts them in Others .. any ideas John?

[amien]

John,

i have this:

AAAAAHH:X3433

i want to map on HH:X

i'v changed your script the : to a | (3 times)

i have also a match value 3433 .. and comes before the HH:X

for some reason value 3433 is not matched and always takes the HH:X

any ideas?