topic Re: Latest Value in QlikView

Latest Value

Tue, 26 Jan 2016 11:21:58 GMT

T1:

PRFID	ACID	Time stamp
100	1	11/10/2013
100	2	11/11/2013
100	3	11/12/2013
100	4	11/13/2014

T2:

ACID	Timestamp	Text
3	11/12/2013	Comment

when am trying to display the latest record by using above tables got the output like below:

Output:

PRFID	ACID	Time stamp	Text
100	4	11/13/2014	-

but the expected result is:

all the values will be showm for the PRFID from two tables.

PRFID	ACID	Time stamp	Text
100	4	11/13/2014	Comment

Re: Latest Value

sunny_talwar — Tue, 26 Jan 2016 19:51:37 GMT

Are you sure both the TimeStamp's are equal? What I mean is that check if one is TimeStamp and other is date or if both are actual timestamps then they would never join with each other.

Try Joining them on Date by may be doing this -> Date(Floor(TimeStamp)) as Date

Re: Latest Value

swuehl — Tue, 26 Jan 2016 22:35:08 GMT

Maybe like attached

A chart with dimension PRFID and three expressions:

=FirstSortedValue(ACID, -[Time stamp])

=Date(Max([Time stamp]))

=FirstSortedValue(Text, -Timestamp)

Re: Latest Value

Qrishna — Tue, 26 Jan 2016 22:44:33 GMT

in the table T2., There's no value in the field 'Text' when the Time stamp is ' 11/13/2014'

Hence u get the out put as

PRFID ACID Time stamp Text

100 4 11/13/2014 -

when you join two tables.

when you have the values in table T2 as

ACID Timestamp Text

3 11/12/2013 Comment

4 11/13/2014 Comment

then the output would be

PRFID ACID Time stamp Text

100 3 11/13/2014 Comment

100 4 11/13/2014 Comment

So check the data first.

Thanks

Re: Latest Value

swuehl — Tue, 26 Jan 2016 22:48:44 GMT

As I read the OPs post, Timestamp and [Time stamp] are two distinct fields, so the tables are not linked by these fields, only by PRFID. You just need to take care only to use PRFID as dimension, and use the three expressions I've posted above.

Re: Latest Value

Qrishna — Tue, 26 Jan 2016 22:54:05 GMT

I thought he was trying to do this

LOAD PRFID,

ACID,

[Time stamp]

FROM

[https://community.qlik.com/thread/201980]

(html, codepage is 1252, embedded labels, table is @1);

join

LOAD ACID,

Timestamp as [Time stamp],

Text

FROM

[https://community.qlik.com/thread/201980]

(html, codepage is 1252, embedded labels, table is @2);

so posted the logic that way.

But not sure what exactly he's trying to do.

thanks

Re: Latest Value

swuehl — Tue, 26 Jan 2016 23:10:02 GMT

Sure, I am also just guessing, only using what the OP posted.

Re: Latest Value

Wed, 27 Jan 2016 05:19:49 GMT

If there is no Time stamp in second table OR The date may blank for some of the records then we cannot get all the records right if we joined based on date.

Re: Latest Value

Wed, 27 Jan 2016 05:20:50 GMT

Dont consider the Time stamp in second table and that is a different field and we are not using it