Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Latest Value

T1:  

PRFIDACIDTime stamp
100111/10/2013
100211/11/2013
100311/12/2013
100411/13/2014

T2:

   

ACIDTimestampText
311/12/2013Comment

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

  Output:  

PRFIDACIDTime stampText
100411/13/2014-

but the expected result is:

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

PRFIDACIDTime stampText
100411/13/2014Comment
8 Replies

Re: Latest Value

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

MVP
MVP

Re: Latest Value

Maybe like attached

A chart with dimension PRFID and three expressions:

=FirstSortedValue(ACID, -[Time stamp])

=Date(Max([Time stamp]))

=FirstSortedValue(Text, -Timestamp)

krishna_2644
Valued Contributor III

Re: Latest Value

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




MVP
MVP

Re: Latest Value

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.

krishna_2644
Valued Contributor III

Re: Latest Value

I thought he was trying to do this

A:

LOAD PRFID,

     ACID,

     [Time stamp]

FROM

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

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

join

B:

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

MVP
MVP

Re: Latest Value

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

Not applicable

Re: Latest Value

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.

Not applicable

Re: Latest Value

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

Community Browser