0 Replies Latest reply: Nov 16, 2017 3:40 AM by Johan R�sing RSS

    Pulling data from a table based on another table

    Johan R�sing

      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;