Lookup on variable length values

[mgendy]

hi,
i have a new challenge in using lookup stage , i want to do the following

* in a lookup table i have variable length data from 3 to 7 characters length , each character represent an information , my stream data contains a little longer string , which i want to lookup for a substring of it starting with the maximum subsctring length which is 7 , if found data for it in lookup table , Continue if it didn't found the data , then take substring of 6 and then go , the following example may illustrate

Lookup data key
0011
0012468
00115
001154
00116

input data
001246899778 ->> i need 0012468 directly from the lookup table

001157894545 ->> here i need 00115 because 0011578 & 001157 are not exist in the table

001174564564 ->> here i need 001 because 0011745 & 001174 & 00117 and 0011 don't exist in the lookup table


kindly help me to find a solution for this issue using lookup stage or any other aporpirate stage , any feedback will be appreciated

Gendy

[chulett]

As far as I know, you'll need cascading lookups - five of them set to continue if they fail. First one checks for a substring of 7, the next 6, etc.

[mgendy]

As far as I know, you'll need cascading lookups - five of them set to continue if they fail. First one checks for a substring of 7, the next 6, etc. ...

Many Thanks chulett for your reply and interest , i've thinked in that but is there is any other method to make it dynamicaly , i'm afraid of any changes happens for the lookup digits in the future , may be they increasd to 8 or 9 ,,,
i really apreciate your support chulett

[chulett]

Hmmm... perhaps match based on three, allow multiple values to be returned from the lookup, order by length desc and take the first match per group?

[Kryt0n]

Or potentially a sparse lookup joining on a substring of the lookup columns length...

[John Smith]

As far as I know, you'll need cascading lookups - five of them set to continue if they fail. First one checks for a substring of 7, the next 6, etc. ...

Many Thanks chulett for your reply and interest , i've thinked in that but is there is any other method to make it dynamicaly , i'm afraid of any changes happens for the lookup digits in the future , may be they increasd to 8 or 9 ,,,
i really apreciate your support chulett

Well if it increase to 8 or 9 change your jobs to match. This is where change management is important. That represents a change to the business rules and should be managed accordingly. It's the same with any other job design;you can only build what you know now. Trying to speculate about the future and trying to make your job bullet proof will take tremendous effort and perhaps not worth the trouble.
Sorry if I can't be more helpful.

[mgendy]

Hmmm... perhaps match based on three, allow multiple values to be returned from the lookup, order by length desc and take the first match per group? ...

that is great idea , but unfortunately it won't work for me , because when takingthe lookup on substring of 3,it will give many results which som of them are false incase of same length, let me sho you

lookup values
001
001234
001451
001982

if i took 001 , and then the greater value then it i will give wrong values

it seem that i'll use cascaded lookups ,, many thanks for your greate support

[chulett]

You could try incorporating some kind of substring check based on result length and key value in the transformer to weed out the false positives... maybe. Sorta.

[Sainath.Srinivasan]

You can do in several ways.

Simplest will be to do something as below

Code: Select all

SELECT max(lpad(matchAreaCode, maxFieldLength,'0'))
FROM    refTable
WHERE  
matchAreaCode = substr(sourceAreaCode, 1, length(matchAreaCode))

[ray.wurlod]

Please make a post telling us, and future searchers, HOW you resolved this issue.