5 Replies Latest reply: Jul 14, 2015 1:33 PM by Sunny Talwar RSS

    how to get the value of a field based on the min() of another field?

    Fraser Anderson

      Hi guys,

       

      I am not sure how to script this one... I have a table containing values from a daily report.   Every day a new report runs (report_date) and for each order_number the requested_date could be the same, or it could change.

       

      I want to use Min() and Group By during my load to create a single new value which corresponds to the first_requested_date. 

       

      In the below example, this would be 30-Jul for order_number 1234

       

      This seems like it should be simple, but I am having trouble figuring it out.

       

      order_numberrequested_datereport_date
      123430-Jul

      29-Jun

      123430-Jul30-Jun
      123410-Jul1-Jul
      123415-Jul2-Jul

       

      The script I am trying:

       

      LOAD

        order_number,

        requested_date vale at min(report_date) as first_requested_date

       

      FROM

      [data.xlsm]

      (ooxml, embedded labels, table is data)

       

      Group by

        order_number