topic Re: Use field to select from database in QlikView

Use field to select from database

marcusbohman — Wed, 25 Nov 2020 16:16:04 GMT

I am selecting PART_ID from a cross reference table with a where clause on PART_NUMBER as below.

Parts:

LOAD*;

SQL SELECT "PART_ID"

FROM "DATABASE1" where PART_NUMBER IN ('84171490','84017929','9519027','9998266');

I would like to use the PART_IDs in another where clause similar to below.

SQL SELECT "PART_ID",

"INFO1",

"INFO2"

FROM "DATABASE2" where IN (Parts.PART_ID);

I have tried a LEFT JOIN but DATABASE2 is to big for me to select everything and then join it.

/Marcus

Re: Use field to select from database

olivierrobin — Tue, 15 May 2018 07:41:14 GMT

hello

why don't you try a single sql select like (syntax may not be correct depending on your base):

SQL SELECT "PART_ID",

"INFO1",

"INFO2"

FROM "DATABASE2" join "DATABASE1"

on "DATABASE2"."PART_ID"="DATABASE1"."PART_ID"

and PART_NUMBER IN ('84171490','84017929','9519027','9998266');

so that the database is working and QV gets only the rows you need

Re: Use field to select from database

Peter_Cammaert — Tue, 15 May 2018 07:49:10 GMT

You can combine the two Selects in a SQL JOIN so that the limited set of rows from table1 is joined with only relevant details from Table2.

A QlikView alternative consists of first storing the list of relevant PART_ID values in a variable and then use that variable in each WHERE clause. For example, something like this (off the top of my head, some tuning may be required):

Parts:

SQL SELECT "PART_ID"

FROM "DATABASE1" where PART_NUMBER IN ('84171490','84017929','9519027','9998266');

PartsList:

LOAD Concat(chr(39) & PART_ID & chr(39), ', ') AS PartsList RESIDENT Parts;

LET vPartsList = peek('PartsList');

DROP Table PartsList;

SQL SELECT "PART_ID",

"INFO1",

"INFO2"

FROM "DATABASE2" WHERE PART_ID IN ($(vPartsList));

Best,

Peter

Re: Use field to select from database

marcusbohman — Tue, 15 May 2018 07:55:18 GMT

Thank you Olivier,

Works like a charm!