Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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

1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

5 Replies
sunny_talwar

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
MVP
MVP

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;

Not applicable
Author

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