topic Re: return a value depending on its time stamp in QlikView

return a value depending on its time stamp

Tue, 01 Apr 2014 11:48:34 GMT

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!

Re: return a value depending on its time stamp

its_anandrjs — Tue, 01 Apr 2014 11:51:53 GMT

New update please check

Use firstsortedvalue or minstring functions

Re: return a value depending on its time stamp

Tue, 01 Apr 2014 11:57:43 GMT

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

Re: return a value depending on its time stamp

sivarajs — Tue, 01 Apr 2014 12:02:21 GMT

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

Re: return a value depending on its time stamp

its_anandrjs — Tue, 01 Apr 2014 12:08:53 GMT

Load Your table like

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

Re: return a value depending on its time stamp

mangalsk — Tue, 01 Apr 2014 12:15:13 GMT

Hello

PFA

It gives following:

Re: return a value depending on its time stamp

Tue, 01 Apr 2014 12:38:05 GMT

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!

Re: return a value depending on its time stamp

Tue, 01 Apr 2014 14:17:35 GMT

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?