Get field from other table in script LOAD

[#1]

Hi all.

I have two tables. My DETAIL table and my RULES table.

DEtail table:

NUM | FAMILIA |      EXAME      |  other fields not importante

x          aa            x-ray leg 2 parts

y          aa            x-ray arm

z          bb               ECO    

and my Rules table are:

FAMILIA | SeachString | %for value

aa               leg                    50

aa               arm                   40

bb               ECO                  12

I need to include in my DETAIL table [% for value] to all records.

I tried the function lookup and it's always give me the exact correspondence between the fields but i need to search in field EXAME [table DETAIL] if exists in ( begin middle or end) the string SearchString[table RULEs ].

I hope my result  is:

DEtail table:

NUM | FAMILIA |      EXAME      |     other fields not importante     ! %for value

x          aa            x-ray leg 2 parts          .....     .....     ....                    50

y          aa            x-ray arm                     .....     .....     ....                    40

z          bb               ECO                           .....     ....     ....                    12

w          bb               BAR                         ..     ...     ...     ...                    null

I work in Qlikview 10 SR2

Best regards
Diogo Pereira

[#2]

I don't know if there's a better way to do this, but I solved it by creating an intermediate table with all possible words in your EXAME field and linking that with your Rules table. Regards,

[#3]

Hello, you can also left join the column from Rules to Detail, and just use 1 table as a result (or perhaps I don't understand your question):
Detail:
load *, RecNo() as Id, SubStringCount(EXAME,' ') AS AmtSpaces inline [
NUM,FAMILIA1,EXAME
x,aa,'x-ray leg 2 parts'
y,aa,'x-ray arm'
z,bb, ECO    
];
Temp_Rules:
load * inline [
FAMILIA,SearchString,PCT
aa,leg,50
aa,arm,40
bb,ECO,12
];
// Kim Waters:
left join (Detail)
load     FAMILIA,   // Joined column
         PCT        // Added column
resident Temp_Rules;
drop table Temp_Rules;