LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ouwen010

Hi, I'm looking for a python based node simular to the Lookup node with a small difference. The Lookup data can contain wildcards like an "*" asterix to indicate any value. I need this for a project where we have discrepancies to validate. This validation is based on a table containing the most important fields. To limit the different combinations wildcards are used when all values for a field are part of the match.

Example lookup data table:
BSSID,C_Region,C_Status,M_Status,N_Region,N_Status ,Result
12,Local,Used,,Local,*,False positive
12,Local,Port_Out,,Local,212,Not Valid

The data input example (limited down here to the same fields):
BSSID,C_Region,C_Status,M_Status,N_Region,N_Status
12,Local,Used,Port_Out,Local,211
12,Local,Used,,Local,211
12,Local,Used,,Local,213
12,Local,Port_Out,,Local,214
12,Local,Port_Out,,Local,212

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: mgajdosik

Depending on complexity of your rules and amount of data you have you have couple of options here. If your lookup data is considerably small and/or you have specific key fields which will not contain wildcard, then you can use left inner join node to join only on fields which do not contain wildcard, and then do the rest of the comparison using brainscript handling wildcard and the rest of the comparison. So you would join on NON-wildcard fields only and in your brainscript you would do check if the field contains asterix (in this case you would not check if the field from other table is the same), and if it does not contain asterix you would do the check. so the script would look like something like this:

# repeat for each of the asterix possible fields
outputRow = 1
if 2:field1!="*" then {
if 1:field1!=2:field1 then outputRow=0
}
if 2:field2!="*" then {
if 1:field2!=2:field2 then outputRow=0
}

emit 1:*
emit 2:*
exclude referencedFields(2, {{^RightInputKey^}})
where outputRow==1

Marek

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: stonysmith

As an alternative, attached is a graph that will do what you need.

The concept here is to match records at the most specific level of detail, and have the lookup node output the matches and also output the records that do match. Then, take the records that were not matched at first, and compare them at the more generic level.

I have used this technique where there were 8+ levels to the hierarchy.. each time, match first on the most detailed level, keep the records that don't match, and work up thru most generic matching at the end.
Attachments:
Generic Match.brg

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ouwen010

The discrepancies contain in some cases 20M records with about 55 fields. The lookup list contains 4k records of 7 fields of which 6 can contain wildcards. So its complex. We are unfortunately still on a version 4, but in the process of upgrading to version 6 next month.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: mgajdosik

Then what you should do is to split your lookup set (since only there are asterixes, if I understood correctly) to the smaller lookup sets where each set will be with asterix within single field ending up with something like this

set1: VAL, VAL, VAL, VAL, VAL, *
set2: VAL, VAL, VAL, VAL, *, VAL

if multiple asterixes are possible then you may have a bit more combinations (which can get a bit ugly), like

set 8: VAL, VAL, VAL, VAL, *, *

etc...

and then perform lookup or join left inner on each of these sets specifying only the fields without asterix. and follow stony's example graph with multiple levels.