Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
New Contributor III

Applymap With Like

Hi,

I have 2 tables:

A

Col

SorDetail

Master

B

Col1

AP_SORDetail

AR_SORMASTER

I want to bring a column in table A which will do a wildmatch search in col1 of B and get the data in new column.

e.g. Col of A has SorDetail ,it will iterate through Col1 and if it finds any match in this case AP_SORDetail it will pull it in new column

Regards,

Tags (2)
2 Replies
Highlighted
MVP
MVP

Re: Applymap With Like

You can't use ApplyMap as far as I can tell - however you can use the WHERE clause to iterate and compare with the LIKE operator. Here is a sample of how it works with a small test dataset:

T1: LOAD RowNo() AS T1.R#,* INLINE [

C1

ABC

123ABC456

ABC123

A@BCDE

ABC@123

];


T2: LOAD RowNo() AS T2.R#,* INLINE [

C2

ABC*

123*

*123*

*12*

*@*

*@1*

@1

];


i = NoOfRows('T2');


T3:

LOAD

  *

WHERE

   Not(IsNull(Match));

LOAD

  If( C1 Like Peek('C2',IterNo()-1,'T2') , 1 ) AS Match,

  T1.R#,

  IterNo() AS T2.R#

RESIDENT

  T1

WHILE

  IterNo()<=$(i)

;

Be aware that if you have a lot of rows in table T2 then this could be time consuming.

For instance if you have  2000 rows in T1 and 2000 rows in T2 it would perform 4 million LIKE comparisons. However when performing it in a single LOAD with WHILE and IterNo() it is rather quick anyway. Do calculate the number of iterations before you attempt a larger number of rows in any or both of the tables to see if it is feasible and test on a smaller subset first to estimate the time it will take to do the full set of rows.

Highlighted
MVP
MVP

Re: Applymap With Like

Here I have attached the application with the small dataset from the answer above along with a larger autogenerated dataset that shows the performance to be pretty good.

T1:

LOAD

RowNo() AS T1.R#,

Chr(Floor(Rand()*26)+65) &

Chr(Floor(Rand()*26)+65) &

Chr(Floor(Rand()*26)+65) &

Chr(Floor(Rand()*26)+65) &

Chr(Floor(Rand()*26)+65) AS C1

AUTOGENERATE

  10000;

 

T0:

LOAD DISTINCT

  '*' & Chr(Floor(Rand()*26)+65) & Chr(Floor(Rand()*26)+65) & Chr(Floor(Rand()*26)+65) & '*' AS C2 

AUTOGENERATE

  100000;


T2:

LOAD

    RowNo() AS T2.R#,

    *

RESIDENT

  T0;

DROP TABLE T0;

   

i = NoOfRows('T2');

T3:

LOAD

  *

WHERE

   Not(IsNull(Match));

LOAD

  If( C1 Like Peek('C2',IterNo()-1,'T2') , 1 ) AS Match,

  T1.R#,

  IterNo() AS T2.R#

RESIDENT

  T1

WHILE

  IterNo()<=$(i)

;


i=;