topic wildmatch and looping though table in QlikView

wildmatch and looping though table

amien — Tue, 16 Feb 2010 22:26:52 GMT

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

wildmatch and looping though table

johnw — Tue, 16 Feb 2010 23:01:39 GMT

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.

wildmatch and looping though table

disqr_rm — Tue, 16 Feb 2010 23:06:34 GMT

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.

wildmatch and looping though table

amien — Tue, 16 Feb 2010 23:17:24 GMT

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

wildmatch and looping though table

johnw — Tue, 16 Feb 2010 23:46:13 GMT

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

wildmatch and looping though table

disqr_rm — Wed, 17 Feb 2010 00:29:29 GMT

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.

wildmatch and looping though table

johnw — Wed, 17 Feb 2010 00:44:08 GMT

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

wildmatch and looping though table

Wed, 17 Feb 2010 02:07:32 GMT

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.

wildmatch and looping though table

johnw — Wed, 17 Feb 2010 02:12:42 GMT

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.

wildmatch and looping though table

Wed, 17 Feb 2010 02:41:51 GMT

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

wildmatch and looping though table

amien — Wed, 17 Feb 2010 07:08:26 GMT

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?

wildmatch and looping though table

amien — Wed, 17 Feb 2010 11:02:23 GMT

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

wildmatch and looping though table

johnw — Wed, 17 Feb 2010 18:28:50 GMT


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.

wildmatch and looping though table

johnw — Wed, 17 Feb 2010 18:29:41 GMT


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.

wildmatch and looping though table

Wed, 17 Feb 2010 22:48:19 GMT

Hi John,

Thanks for pointing this out.

Nimish

wildmatch and looping though table

rwunderlich — Thu, 18 Feb 2010 01:35:12 GMT

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

wildmatch and looping though table

johnw — Thu, 18 Feb 2010 01:44:38 GMT

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

wildmatch and looping though table

Thu, 18 Feb 2010 02:06:53 GMT

Rob,

Thiis is extraordinary!. Very very clean solution.

Nimish

wildmatch and looping though table

amien — Fri, 19 Feb 2010 15:20:23 GMT

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?

wildmatch and looping though table

amien — Fri, 19 Feb 2010 15:48:25 GMT

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?