Sequential Data pull format

[hobanwashburne]

I’m pulling data from an AS400 (ODBC) and what I would like
to do is use the results of one query to drive the next (or a group). So here’s
my current methodology:

// Query establishing master list

TblPartsList:

LOAD

Field01 as [PartNumber],
Field02 as [ItemDesc],
Field03 as [ListPrice],
Field04 as [Company];

SQL SELECT *
FROM <Blah Blah DB Table>

WHERE Field02 = <SOMETHING> AND Field04 = <SOMETHING> ;

//Query pulling related information from another table

LEFT JOIN (TblPartsList) LOAD

Field05 as [PartNumber],
Field06 as [Whse],
Field07 as [Qty];

SQL SELECT *
FROM <Blah Blah DB Table>

WHERE Field06 = <SOMETHING> ;

The result is a large single table that contains both part
description data (static) as well as inventory data where the same part could
exist in multiple warehouses.

What I believe I want is two tables that are linked (joined)
on the PartNumber field. Or 3 tables one containing part numbers only, one
having the description data, and the last having the inventory data. Can
someone point me in the right direction? Or am I thinking about this the wrong way?

[Gysbert_Wassenaar · Accepted solution]

If you want two tables then you need to use left keep instead of left join. Also the second select statement will retrieve all the records and Qlikview will later match the records with those of the the first table. You could rewrite the second sql statement so a join is done on the database server to limit the number of records returned.

Left Keep LOAD

T1.Field05 as [PartNumber],
T1.Field06 as [Whse],
T1.Field07 as [Qty];

SQL SELECT *
FROM <Blah Blah DB Table1> T1, <Blah Blah DB Table2> T2

WHERE T1.Field05 = T2.Field01

AND T1.Field06 = <SOMETHING>

AND T2.Field02 = <SOMETHING>

AND T2.Field04 = <SOMETHING>

;