Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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
Maybe like attached
A chart with dimension PRFID and three expressions:
=FirstSortedValue(ACID, -[Time stamp])
=Date(Max([Time stamp]))
=FirstSortedValue(Text, -Timestamp)
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
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.
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
Sure, I am also just guessing, only using what the OP posted.
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.
Dont consider the Time stamp in second table and that is a different field and we are not using it