Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
ynottableau
Contributor II
Contributor II

result referenced table to show only entry of lastest date

Hi All,

I have 2 tables. Table #1 contains the primary form. The table #2 tracks the status of those form as they are routed. I want to construct a third table linking the previous two, but the table #3 should only show the latest status based on the date of tab#2.

Name ID
RefA aa1
RefB aa2
RefC aa3

 

ID Status Date
aa1 Completed 5/12/2023
aa1 Pending 5/9/2023
aa1 Started 5/1/2023
aa2 Pending 5/6/2023
aa2 Started 5/5/2023
aa3 Removed 5/10/2023
aa3 Started 5/1/2023

 

Result

Name Status
RefA Completed
RefB Pending
RefC Removed

 

I've tried ({$<%Date = {"$(=Date(Max(%Date), 'M/DD/YYYY'))"}>} Status)

Labels (1)
1 Solution

Accepted Solutions
Kushal_Chawda

@ynottableau  use below measure for status

=FirstSortedValue(DISTINCT Status, -Date)

View solution in original post

10 Replies
Kushal_Chawda

@ynottableau  use below measure for status

=FirstSortedValue(DISTINCT Status, -Date)
SunilChauhan
Champion II
Champion II

link two table with ID then use Dimension as Name and Expression below

 

 

FirstSortedValue(Status, -Date)

 

This will do i think

Sunil Chauhan
NiTo
Creator
Creator

Hi Sunil and Kushal.

Even I tried it and it is not working.

Can you please, tell me why we are using -Date function here? If we are using FirstsortValue. I am new to qlik sense to I have this doubt.

Thanks

SunilChauhan
Champion II
Champion II

- sign for descending order and  normal i.e + ve for ascending order sorting 

 

for ex;  

 

we have   Name Status Date

aa    Text1  14-6-2023

 aa    Text2  15-6-2023

 

if you use - in firstsortedvalue then result in latest date  i.e  15-6-2023

and if  use firstsortedvalue( Status,-date)  it return   Text2 

firstsortedvalue( Status,date)  it return   Text1

Sunil Chauhan
NiTo
Creator
Creator

Thanks Sunil for the clarification, really useful.

But the coding is not working. Any idea why?? I tried both. But the column is blank. Expression editor do accept both the coding.

thanks

SunilChauhan
Champion II
Champion II

 use firstsortedvalue( Status,-num(date)) or  change format of date to  DD-MM-YYYY then apply below codes

  firstsortedvalue( Status,-date)

Sunil Chauhan
SunilChauhan
Champion II
Champion II

or try this  Maxstring({$<Date = {"$(=Date(Max(Date), 'MM-DD-YYYY'))"}>} Status)

Sunil Chauhan
NiTo
Creator
Creator

Hi Sunil,

I used the following code to change the date format: 

Date(Date#(Date,'MM-DD-YYYY'),'DD-MM-YYYY') as StatusDate

Unfortunately, it is not working. Please check the attached file with the response.

Thanks

Kushal_Chawda

@NiTo  your date is in 'MMM DD YYYY' format.. so use below

Date(Date#(Date,'MMM DD YYYY'),'DD-MM-YYYY') as StatusDate