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

    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:

    Capture.PNG

     

     

    Hope you find this helpful !

    Omar Ben Salem.