result referenced table to show only entry of lastest date

[ynottableau]

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)

[Kushal_Chawda · Accepted solution]

@ynottableau  use below measure for status

=FirstSortedValue(DISTINCT Status, -Date)

[SunilChauhan]

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

FirstSortedValue(Status, -Date)

This will do i think

[NiTo]

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]

- 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

[NiTo]

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]

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

  firstsortedvalue( Status,-date)

[SunilChauhan]

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

[NiTo]

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