topic Re: Applymap With Like in QlikView

Applymap With Like

rahulsingh12 — Wed, 17 Oct 2018 06:38:12 GMT

Hi,

I have 2 tables:

Col

SorDetail

Master

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,

Re: Applymap With Like

petter — Wed, 17 Oct 2018 07:35:43 GMT

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.

Re: Applymap With Like

petter — Wed, 17 Oct 2018 08:06:53 GMT

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