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

[#1]

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_number	requested_date	report_date
1234	30-Jul	29-Jun
1234	30-Jul	30-Jun
1234	10-Jul	1-Jul
1234	15-Jul	2-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

[sunny_talwar · Accepted solution]

Try this:

LOAD order_number,

          FirstSortedValue(requested_date, -requested_date) as first_requested_date

FROM

[data.xlsm]

(ooxml, embedded labels, table is data)

Group by order_number

[sunny_talwar]

Or this:

Table:

LOAD order_number,

    Date#(requested_date, 'DD-MMM') as requested_date,

    Date#(report_date, 'DD-MMM') as report_date

FROM

[https://community.qlik.com/thread/172519]

(html, codepage is 1252, embedded labels, table is @1);

Join(Table)

LOAD order_number,

  FirstValue(requested_date) as first_requested_date

Resident Table

Group By order_number

Order By order_number, requested_date desc;

[maxgro]

another solution (no group by, no min)

source:

LOAD order_number,

     requested_date,

     report_date

FROM

[https://community.qlik.com/thread/172519]

(html, codepage is 1252, embedded labels, table is @1);

final:

NoConcatenate load

  order_number,

    requested_date as first_requested_date

Resident source

Where order_number <> Peek(order_number)

order by order_number, requested_date desc, report_date desc;

DROP Table source;

[#5]

Great, simple and quick

      FirstSortedValue(requested_date, order_date) as first_requested_date

This returned the correct value.   Since I am using GROUP BY for some other values, this solution works the best for me.

Can I use:

    LastValue(requested_date) as latest_requested_date

to get the most up to date value?  I am going to try that out right now!

Thanks very much!

[sunny_talwar]

You will need a Order By statement when you use FirstValue