Linking two tables with variable length key?

[#1]

I have two tables, one has a full Identification number and the second has a partial number - the partial could be length of 2, 3, 4, etc

Basically - I want to compare if Left(Indentification_Num,len(Partial_Num)) = Partial_Num

First:

Indentification_num

other fields

Second:

Partial_num

other fields

Is there a way to do this in the load script?

thanks

[MayilVahanan]

HI

Is there any field common in both table?

if so, then join with that. After that, compare identification number and partial number . Can you elaborate your table structure?

[#3]

No other common fields, that is the problem I am having.

[MayilVahanan]

HI

Create the key for both the tables and join based on that key. Then compare it.

[#5]

Maybe I am not being really clear.  Table1 are all 10 digits.  Table 2 can be between 2-10 digits.  So the Bold in table one are the ones that would matchup.

Table1

Indentification_num

1234567891

1237346731

1453626723

1231461234

2345555555

2366262662

2388888888

3452626127

3453777777

Table2

Partial_num

123

23

3453

[rwunderlich · Accepted solution]

Here's one possible solution. See attached example.

FOR i = 0 to NoOfRows('Table2')-1

LET vPartial = peek('Partial_num', $(i), 'Table2');

LET vLink = peek('%Link', $(i), 'Table2');

Table1_Matches:

LOAD

          Indentification_num

          ,$(vLink) as %Link

RESIDENT Table1

WHERE Indentification_num LIKE '$(vPartial)*'

;

NEXT i

-Rob

[#7]

Thanks for the responses.  Rob that looks like it will work for me.  I appreciate it!