get date of max value

[#1]

Hello,

i would like to get the date of a maximum value in a textfield "=max(blabla2)"

How can i get the date of this value?

Here is the part of my loading script:

TCSCC2:

LOAD *,

date(TCSCC2DATEDMY, 'DD.MM.YYYY') as blabla2DMY,

TCSCC2BSMID1+TCSCC2BSMID2......... as blabla2

FROM [tcdata\*-tcscc2.qvd] (qvd);

best regards

Ingo

[khadeer]

Hi,

date(Max(ur Datefield),DD-MM-YYYY)

[er_mohit]

HIIIIIII

use max function in your  text object

=max(blabla2) as max_Date  or

=max(blabla2,'DD-MM-YYYY')as max_date

it works

but in script level,use of aggrigation function(like min,max,sum) with the help of order by, group by or where which seems like to aggregate.

hope it helps

[#4]

Hi it does not work, because blabla2 has a value for each day of produced cards in our company.

-> blabla2 is the sum of the daily production of different card typs TCSCC2BSMID1+TCSCC2BSMID2 and so on.

I have a sepperated date in

date(TCSCC2DMY, 'DD.MM.YYYY') as blabla2DMY

which holds my date.

when i'm searching for my maximum Number of produced cards, i would get my amount by =max(blabla2)  (in my case, it would be 1614 ), but how can i get my date from this amount out of blabla2DMY ? While searching through my database by hand, it would be the 5th of January 2013

[er_mohit · Accepted solution]

LOAD *,

max(blabla2)as vmax,

FirstSortedValue(blabla2DMY,-blabla2)as max_date;

[#6]

load

max(blabla2) as max_blabla2,

FirstSortedValue(blabla2DMY, -blabla2) as DateOfmaxvalue;

load blabla2,

         date(TCSCC2DMY, 'DD.MM.YYYY') as blabla2DMY

from table1