return a value depending on its time stamp

[#1]

Hi!

I'm trying to write an expression that per Interest ID returns only the activity with the "earliest" time stamp. how do i do that?

i.e. from the example above we should ideally get:

123    PQ 2012-05-15

133    PQ 2012-02-19

3333  IM 2013-05-08

very grateful for your help!

[its_anandrjs]

New update please check

Use firstsortedvalue or minstring functions

[#3]

Create a st. table chart

Dim ID, activity

Exp : Count({<Date = {$(=Date(Min({1} Date),'YYYY-MM-DD'')}>}ID)

Go to presentation tab and the hide the exp column.

Thanks,

Prabhu

[sivarajs]

Add the columns in table and use aggr(Min(Date), Activity) in expression

[its_anandrjs]

Load Your table like

T:

LOAD date#(date,'YYYY-MM-DD') AS date,IterestId,activity;

load *Inline

[

IterestId,activity,date

123,Web,2013-03-05

123,PQ,2012-05-15

123,IM,2013-10-11

133,PQ,2012-02-19

3333,BR,2013-12-10

3333,IM,2013-05-08

];

Left Join

LOAD

IterestId,

MinString(date) as MidDate,

Resident T

Group By IterestId;

take table box

And in Dimension -> IterestId

or Dimension -> MidDate

[mangalsk]

Hello

PFA

It gives following:

[#7]

hi & thanks Anand!

can you help me with an example of the syntax for firstsortedvalue? (i'll create an aggregated dimension in my chart table)? i believe that should do the trick!

thanks a lot in advance!

[#8]

thanks everyone for the input. due to the size of the tables and the number of rows, adding columns in the script is not likely to be an option.

I've figured out firstsortedvalue, and it returns the date of the first activity per interest ID. very nice.

what I'm still not able to do, is to return the field (say "activity" from the example above) that is associated with that date (that has in fact generated the date in the first place)

ideally, based ont he example above, I'd like to write something like:

firstsortedvalue(interest_id,date, activity). The expression would check and return which activity that took place first, per interest ID.

doable?