topic Re: Linking two tables with variable length key? in QlikView

Linking two tables with variable length key?

Wed, 27 Feb 2013 21:25:50 GMT

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

Re: Linking two tables with variable length key?

MayilVahanan — Wed, 27 Feb 2013 21:30:15 GMT

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?

Re: Linking two tables with variable length key?

Wed, 27 Feb 2013 22:17:38 GMT

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

Re: Linking two tables with variable length key?

MayilVahanan — Wed, 27 Feb 2013 22:30:25 GMT

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

Re: Linking two tables with variable length key?

Wed, 27 Feb 2013 22:37:13 GMT

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

3453

Re: Linking two tables with variable length key?

rwunderlich — Thu, 28 Feb 2013 05:12:31 GMT

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

Re: Linking two tables with variable length key?

Thu, 28 Feb 2013 13:42:13 GMT

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