1 Reply Latest reply: Dec 6, 2012 9:44 AM by Dave Riley RSS

    Script: Load data and reduce based on input from an Excel file.

    Daniel Wewerka

      Dear Ladies and Gentlemen,

       

      I would appreciate your help on the topic following.

       

      OrderLocation
      CostCenter
      1USAA0001
      3ChinaB0003
      7ChinaC4032
      8USAK3003
      8SwedenL3003

       

      I'm loading in multiple Excle Files. All of them have a Orders and Cost Centers - as show above. This is about 500k of lines.

      Then I've got another Excel Files, where all Cost Centers are mentioned, which I finally need.

       

      How can I reduce my data set to only those cost Centers I need using the script.

       

      A WHERE-Clause in the LOAD part is not appropirate as I would need to mention about 1000 Cost Centers.

       

       

      Thank you for your help.

        • Re: Script: Load data and reduce based on input from an Excel file.
          Dave Riley

          Hi,

           

           

          There's a few ways to do this but I find an INNER JOIN the easiest ...

           

          Costcenters:

          Load * inline [

          CostCenter

          A0001

          C4032];

           

          INNER JOIN (Costcenters)

          Load * Inline [

          Order,    Location,    CostCenter

          1,    USA,    A0001

          3,    China,    B0003

          7,    China,    C4032

          8,    USA,    K3003

          8,    Sweden,    L3003];

           

          ... this example will leave you with just data for Orders 1 & 7.

           

          flipside