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
];

[johnw]

Do you need to be able to load in the team table, or can it be hardcoded?

pick(1+wildmatch(string,'*LS01*','*LS02*','*LS03*'),'Others','Team 1','Team 2','Team 3') as Team

It's not technically much different than nested ifs, and might be harder to maintain, but I'm guessing it's faster. There has to be a more elegant solution that's still fast, though.

[disqr_rm]

Here you go.

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
];
let MaxVal = NoOfRows('table1') -1;
for i = 0 to MaxVal
 table3:
 LOAD string, value, peek('description', $(i), 'table1') as team resident table2
 WHERE wildmatch(string, peek('match', $(i), 'table1')) > 0;
next i
drop tables table1, table2;

I would use this logic for smaller set of data though. I am sure there are better ways of doing it. I will wait to hear other suggestions.

Hope this helps.

[amien]

Thanks Rakesh..

But what if i add a value to table2: TTLS05QQ, 10

there is no match on tabel1. i want the records with no match, put in the team : others

[johnw]

How about combining the two approaches? Use a loop to generate the pick(match()) from the table. You get the load performance of a hardcoded solution, the 'Other' team, and you still get to load your table from whatever source you want:

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

let MaxVal = NoOfRows('table1') -1;
let vmatch = 'pick(1+wildmatch(string';
let vteams = '),' & chr(39) & 'others' & chr(39);

for i = 0 to MaxVal
let vmatch = vmatch & ',' & chr(39) & peek('match' ,$(i),'table1') & chr(39);
let vteams = vteams & ',' & chr(39) & peek('description',$(i),'table1') & chr(39);
next i

let vmatch = vmatch & vteams & ')';

DROP TABLE table1;

table2:
LOAD *
,$(vmatch) as description
INLINE [
string, value
AALS01DD, 10
ABLS01EE, 20
ACLS02GG, 50
LLLS02EE, 10
RRLS03QQ, 50
TTLS05QQ, 10
];

[disqr_rm]

Here you go:

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
 TTLS05QQ, 10
];
let MaxVal = NoOfRows('table1') -1;
for i = 0 to MaxVal
 table3:
 LOAD string as newstring, value, peek('description', $(i), 'table1') as team resident table2
 WHERE wildmatch(string, peek('match', $(i), 'table1')) > 0;
next i
concatenate(table3)
LOAD string as newstring, value, 'Others' as team
RESIDENT table2
where not exists('newstring', string);
drop tables table1, table2;

I am sure code from John is working as well. Thanks John.

[johnw]

Here's another answer. I think I like this one better than than the generated pick(match()), but you'd have to check the performance vs. the other options, as I'm not sure how fast all these string operations are. It's also a little less flexible than the wildmatch, but might be plenty for your purposes.

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

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
];

[#8]

Hi Amien,

In your particular case, I would use the following method. It is simple and not too much performance issue (a bit because string manipulation is involved).

The basic logic in your table and field structure is that the matching values in string are in specific positions within the sting values.

2-4-2. The 4 charachters are the ones that need to be looked up. So I have used a simple applymap function.

table1:
mapping 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
 TTLS05QQ, 10

];
table3:
Load *,applymap('table1',mid(string,3,4),'others') as Desc resident table2;
drop table table2;

Let me know if this helps. Again, this will work only if all your string values are structured the way you have given in the example.

[johnw]

Assuming you're right that the matching values are always in specific positions, I agree that yours is the correct solution. Well, technically I'd just add the new field to the original load of table 2 instead of creating a table 3, but the applymap is the way to go if you're right about the data format.

[#10]

Hi John,

You mean the following way

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

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

];

Nimish