7 Replies Latest reply: Oct 9, 2016 9:22 AM by Massimo Grossi RSS

    How to load in old n new data , and use where not exists?

    Jim Chan

      Hi Guys,

       

      I want o use Where not Exists for my Order and New Order table.

       

      I have loaded Order_Table and I have transformed and added some new field. Then found out i have another New_Order table which vae some new records need to load in as well. So i wrote the script below. But when i reloaded it, it created Syntetci key.

       

      How to avoid that and able to load in both Order and New Order table records?

       

       

      Orders:

      LOAD

      EmployeeID &'|' & Year as order_key,

      OrderID,

           Month(OrderDate) as Month,

           OrderDate,

           EmployeeID,

           CustomerID,

           ProductID,

           APPLYMAP('Product_Details',ProductID,'ProductID Not Available in Product Details') as Product_Made,

           //APPLYMAP('Product_Details',ProductID,null()) as Product_Made,

           ShipperID,

          Year,

           Sales,

           Cost,

           Quantity   

         

      FROM

      [Data Source\Order.xlsx]

      (ooxml, embedded labels, table is Order);


      New_Order:

      LOAD OrderID,

           OrderDate,

           EmployeeID,

           CustomerID,

           ProductID,

           ShipperID,

           Year,

           Sales,

           Cost,

           Quantity

      FROM

      [Data Source\New_Order.xlsx]

      (ooxml, embedded labels, table is Order)

      where not exists(OrderID);

       

      synthetic_key.jpg

       

      Rgds

       

      Jim