LAE

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

Originally posted by: jodycrutchfield

I am trying to narrow a data set based on a single lookup data in a static node.

The problem is I don't know how long the lookup data is going to be.

For Example:
Set 1
Fruits,F2,F3,F4
apples,1,1,1
appliances,2,2,2
apricots,3,3,3

lookup value could be (only one value will be used at a time)
app - which should give apples, appliances
or
appli - which should give appliances

I have attached a graph that demonstrates what I am trying to do using a lookup node. Is there a way to do this?

(I know appliances is not a fruit)

Thanks,

Jody
Attachments:
VaryingLengthLookup.brg

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

Originally posted by: jodycrutchfield

Ok, I used a python node to solve this problem.

Attached is a graph that shows the problem and the solution.

If anyone has a better way of doing this please let me know.

Thank you,

Jody
Attachments:
VaryingLengthLookup-Solved.brg

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

Originally posted by: michaelslowey

I've done something similar.

For example I had a list of Postal codes (which in the UK represent a particular road and a set of houses on that road) that where considered to be "hot" ie. fraudulent. This list contained Full postcodes or it could be part of a postcode (which would represent not just a street and a set ouf houses but a SET of streets).

I had a set of Customer Records that had a Full Postcode and I had to match em to the Full or Part Postcode in the "Hot" Postcodes file.

Here's how I solved it...

I created a comma seperated list of the values in the Static Datausing an Aggregate node. I then added this field to the Main Data using a join (I use the lookup value as 1 and 1 in the join)

I then used a Filter Node to to split the LookUp data into a list and use a loop to do the matching...

Ive attached an excample graph...

Hope this helps.
Mick
Attachments:
Variable Length Lookup.brg

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

Originally posted by: jodycrutchfield

Very nice,

Thank you,

The python node I created will allow you to search the middle or end of the string for values as well as the beginning, if needed.

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

Originally posted by: ejones

You might be able to solve this using a regular expression.

It requires building the regular expression by collapsing the lookup table into one single regular expression.
Attachments:
VaryingLengthLookup-Solved2.brg

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

Originally posted by: alyon

Instead of using the complicated regular expression or Python node, should we not achieve the desired result using the lookup node with following values -

InputKey - 1
LookupKey - 1

Script -
emit 1:*
where Market == Fruit.left(Market.len())


May be I did not understand the problem fully. If I did then the above should solve the problem.

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

Originally posted by: mgajdosik

I used the same approach with keys 1 and 1 and then where condition will do the matching instead. I just run into problems when the datasets were too large, if I understood Rich properly he explained to me that if you do so, then entire dataset will be loaded into memory.

I tried 8 mil. records with lots of fields match against 4 records only and it failed running out of memory.

And other thing you need to be aware of is the number of matches that will be done. So even if the datasets are not that big, but both are 1 mil records only with few fields. Then 1 mil will be compared one by one to another one mil., which may take forever.

Marek