0 Replies Latest reply: Sep 13, 2011 10:07 AM by Donald Posthuma RSS

    Select table lines based on condition and generic field

    Donald Posthuma

      Hi there,

       

      I have the following issue. I want to select a subset of data based on a condition and a group by of a field. So I have several lines with the same ID but I only want to select one of these fields. Below there is a part of the script. T_Key is the generic field and I only want to have the line where this condition holds:

      MIN(FABS(PlannedArrivalDT-OrderDT))


      However other fields, for instance Optimaldeliveryquantity, have different values for the lines belonging to the same T_Key. I only want to keep the value which belongs to the line where the condition holds. Anybody got a suggestion?

       

      ORDERDETAIL_DP:
      LOAD
                   
      Company & OrderDetailID AS OrderDetailID,
           
      NUM(OrderDT) AS OrderDT,
           
      ExtOrderLineNb,
           
      Company & APPLYMAP('MAPDPTS',OrderID) & StorageID AS T_Key,

            APPLYMAP('MAPDPDT', OrderID) AS PlannedArrivalDT,
           
      MinOrderQty AS OrderDetail.MinOrderQty,
           
      OptimalOrderQty AS OrderDetail.OptimalOrderQty,
           
      MaxOrderQty AS OrderDetail.MaxOrderQty,
           
      BridgingOrderQty AS OrderDetail.BridgingOrderQty,
           
      InStorageQty AS OrderDetail.InStorageQty,
           
      StoragePct AS OrderDetail.StoragePct,
           
      Urgency AS OrderDetail.Urgency,
           
      StorageID AS OrderDetail.StorageID,
                 
      CreateDT AS OrderDetailCreateDate
      FROM
      ORDERDETAIL_DP.QVD(
      QVD)