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]

problem 1: if string contains a ":" .. fixed by changing to |

problem 2: when a string was matched twice. seems like the one with first hit on character position it a match:

LS03, team3
S03Q, team4

with string : RRLS03QQ, 5

always hits team3 first. Because LS03 is sooner in the string then S03Q

what i need is that i need the lastest or the first hit from the linein (i can re-arrange my linein if needed)

table1:
MAPPING LOAD
 match
,'|' & description & '|' as description
INLINE [
 match, description
 LS01, team1
 S03Q, team4
 LS02, team2
 LS03, team3
 S09Q, team4
];

table2:
LOAD *
,rangeminstring('others',subfield(mapsubstring('table1',string),'|',2)) as description
INLINE [
 string, value
 AALS01DD, 10
 ABLS01EE, 20
 ACLS02GG, 50
 LLLS02EE, 10
 RRLS03QQ, 50
 TTLS05QQ, 10
 F:LS02BR, 20
 S09QEEEE, 40
];

[amien]

Rob,

i'v tested your script with first loading the full table and then to map a resident load .. but still takes half an hour on 3mil records and 500 match-posibilities

[amien]

MapSubstring( 'mapname' , expr )
This function can be used for mapping parts of any expression on a
previously loaded mapping table. The mapping is case sensitive and
non-recursive. The substrings are mapped from the left to the right.

so .. its useless to my a match higher or lower in the map list .. right to left would also save the day for me.

[#5]

Hi Rakesh,

Could you please help me out if the problem is to find one to many matches? Here is an example:

table1:

LOAD * INLINE [

match, description

*LS01*, team1

*LS02*, team2

*LS03*, team3

];

table2:

LOAD * INLINE [

string, value

AALS01DDLS02, 10

ABLS01EELSO3, 20

ACLS02GG, 50

LLLS02EE, 10

RRLS03QQ, 50

TTLS05QQ, 10

];

The output required is:

AALS01DDLS02, 10    team 1

AALS01DDLS02, 10    team 2

ABLS01EELSO3, 20   team 1

ABLS01EELSO3, 20   team 3

ACLS02GG, 50          team 2

LLLS02EE, 10           team 2

RRLS03QQ, 50         team 3

TTLS05QQ, 10           others