topic Re: Get field from other table in script LOAD in QlikView

Get field from other table in script LOAD

Thu, 06 Oct 2011 16:46:19 GMT

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

Re: Get field from other table in script LOAD

Thu, 06 Oct 2011 17:30:40 GMT

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,

Re: Get field from other table in script LOAD

Thu, 06 Oct 2011 18:56:06 GMT

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;