Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

johanru1
New Contributor

Pulling data from a table based on another table

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;

Community Browser