Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
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.
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=;