Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
amien
Specialist
Specialist

wildmatch and looping though table

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


23 Replies
johnw
MVP
MVP

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
Partner
Partner

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
Specialist
Specialist
Author

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
MVP
MVP

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
Partner
Partner

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
MVP
MVP

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

Not applicable

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
MVP
MVP

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.

Not applicable

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