4 Replies Latest reply: Aug 30, 2011 8:19 AM by Goran Korsgren RSS

    Loop files and variables

    Donald Posthuma

      Hi there,

       

      I've seen some some questions about this problem but they don't seem to suit my particular problem. I have the following issue. I have several data sources from separate business units. They all contain the same data fields and they need to be loaded in the same table but all need a qualifier within the table to retrieve the original source. So I have for two business units:

       

      FUELACTIVITY.QVD

      GASACTIVITY.QVD

       

      with qualifiers

       

      'Fuel'

      'Gas'

       

      The script for FUEL looks as shown below

       

      T_ORDERS:
      LOAD #OrderID,
          
      ArgosCompany,
          
      OrderDate,
          
      CustomerLocation,
          
      SUM(OrderQuantity) AS OrderQuantity
      GROUP BY
       
      #OrderID, ArgosCompany,OrderDate,CustomerLocation;
      LOAD SiteId & DATE(ShiftDate,'DDMMYYYY') AS #OrderID,
                 'Fuel'
      AS ArgosCompany,
          
      DATE(ShiftDate, 'DD-MMM-YYYY') AS OrderDate,
          
      SiteId AS CustomerLocation,
          
      IF(Activity = 'Load', LoadedQty, DeliveredQty) AS OrderQuantity
      FROM
      FUELACTIVITY.QVD
      (
      qvd)
      WHERE(MATCH(Source,'DP','DC') AND ((Activity = 'Load' OR Activity = 'Delivery') AND (LoadedQty > 0 OR DeliveredQty > 0)));


      How can I incorporate the looping over several files with qualifiers? If this can be done using an Inline load that would work perfect since there is only a limited number of business units but using an xls could also work. Thanks in advance!

       

      Cheers Donald