Qlik Community

Ask a Question

New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Qlik Highlights 2020 Giveaway! Watch, reply and have a chance to win a $200 Amazon Gift Card! Watch Video
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

New update please check

Use firstsortedvalue or minstring functions

Not applicable

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

Specialist II
Specialist II

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

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

Creator III
Creator III

Hello

PFA

It gives following:

Untitled.png

Not applicable

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

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?