Pulling data from a table based on another table

johanru1 — Thu, 16 Nov 2017 08:40:05 GMT

Hi,

I have been stuck trying out several different solutions for my issue, but can't seem to figure out where I am going wrong:

I have 3 table:

Excel spreadsheet with 2 data points (Machines)
- Serial number
- Machine name
DB with three data points (Order)
- Order number
- Serial number
- Customer number
DB with two data points (Customer)
- Customer number
- Customer name

I have already matched up the serial numbers in Machines and Order to give me a unique Order number based on the Serial number by using NoConcatenate and dropping the order table.

The problem I am facing at this point, is that the Customer table has nothing to match with when the Order table is dropped and therefore gives me rubbish when I add the Customer name to my visualization.

The short and sweet code as of now:

Machines:

LOAD SerialNumber,

"Machine name:"

FROM [xxxx.xlsx]

(ooxml, embedded labels, table is Sheet1);

Aktor:

LOAD Nm,

CustNo;

SQL SELECT Nm,

CustNo

FROM dbo.Customer;

Order:

LOAD distinct OrdNo,

CustNo,

R10 as SerialNumber;

SQL SELECT OrdNo,

CustNo,

R10

FROM dbo."Order";

NoConcatenate

New:

LOAD

SerialNumber,

Max(OrdNo) as OrdNo

Resident Ordre1

where SerialNumber > 10

Group By SerialNumber Order By SerialNumber asc;

DROP Table Order;

topic Pulling data from a table based on another table in QlikView

Pulling data from a table based on another table