Search for occurances - Generic Script for Qlik by Omar BEN SALEM.

[OmarBenSalem]

Hi all,

The last few days I was facing a harsh problematic.

In fact, I was trying to join 2 tables while there was no possible direct link.

To explain in better worlds:

Table 1 had a composite Key1 (Exp: |xx|02| )

while

table 2 had another Key2 (Exp :|xx|1919|15|02|)

The goal with to join these 2 values since the xx and 02 from Key1 exists in Key 2 :|xx|1919|15|02|.

I wanted to share with you a generic script that everybody could use if you ever face such a situation.

Ps: the Key1 and Key2 are composed by a concatenation of 3 fields.

The 3 fields are NOT ALWAYS available, some lines have blank values.

In order for this script to work, we have to create the Key1 and Key2 in a way to ALWAYS seperate the values by '|'.

Exp:

|xx|

|xx|02|

|dd|02|abc|

Concrete way to do so :

if(len(Trim(Field1))=0 and Len(Trim(Field2))=0,'|'&Field3&'|',

if(len(Trim(Field1))=0 and Len(Trim(Field2))<>0 ,'|'&Field3&'|'&Field2&'|',

if(len(Trim(Field1))<>0 and Len(Trim(Field2))=0 ,'|'&Field3&'|'&Field1&'|',

('|'&Field3&'|'&Field1&'|'&Field2&'|')))) as key

I hope you would try it and let me know if you like it !

Here we go :

Table1:

load * inline [

Key1 , id, otherField

|xx|02|,1,Field1

];

Table1_keywoords0:

NoConcatenate

LOAD id,trim(text(subfield(Key1,'|'))) as kw

resident Table1;

Table1_keywoords:

NoConcatenate

LOAD id,kw

resident Table1_keywoords0

where isnull(kw)=0 and kw<>'' and trim(kw)<>'';

drop table Table1_keywoords0;

left join(Table1)

LOAD id,count(DISTINCT kw) as keycount,count(DISTINCT kw) as countmatchthr

resident Table1_keywoords

group by id;

//******************************** Table2 **********************************************

Table2:

load

*, rowno() as Table2Id inline

[

Key2, value1, value2

|xx|1919|15|02|, 100,200

|xx|1919|15| , 200,300

1919|15|02|,100,50

];

left join(Table2)

LOAD id,// as idfk,

text(kw) as kw2//,countmatchthr

resident Table1_keywoords;

table3_prefilter:

NoConcatenate

LOAD *,

if(index('|' & Key2,'|'&kw2&'|')>0,1,0) as ismatch

resident Table2;

mainfact0:

NoConcatenate

LOAD distinct Table2Id,id,kw2,1 as ismatch Resident table3_prefilter where ismatch=1;

drop table table3_prefilter;

left join(mainfact0)

LOAD id,countmatchthr

Resident Table1;

mainfact1:

LOAD Table2Id,id,countmatchthr,count(ismatch) as countmatchnot

resident mainfact0

group by Table2Id,id,countmatchthr;

drop table mainfact0;

mainfact:

NoConcatenate

LOAD Table2Id,id resident mainfact1 where countmatchthr=countmatchnot;

drop table mainfact1;

drop fields id from Table2;

RESULT:

Hope you find this helpful !

Omar Ben Salem.