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

return a value depending on its time stamp

Hi!

2014-04-01_1341.png

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!

7 Replies
its_anandrjs

New update please check

Use firstsortedvalue or minstring functions

Not applicable
Author

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
Specialist II
Specialist II

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
Creator III
Creator III

Hello

PFA

It gives following:

Untitled.png

Not applicable
Author

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!

Not applicable
Author

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?