Finding one Value of with Max Date

[#1]

Hi all

I have a straight table i need to pull only 1 value based on max date and project. suggest me

Script used to derive the data.

Trans3:
Load
ProjectID_SYS,
[Updated On],
[Value Before Update] as N_Customer_Traction_Status
Resident [Project Metrics History]
where [Metric Name]='Customer Traction Status';

Trans4:
load
ProjectID_SYS,
if([Metric Name]='Customer Traction Status',Timestamp(Max([Updated On]),'MM/DD/YYYY hh:mm:ss TT')) as Maxmetric
Group by ProjectID_SYS,[Metric Name];

[qlikmsg4u]

tried the below attachment?

[#3]

Thankyou KN

I am looking for max date with corresponding value with only one record , even if there are no.of records with same id.

[#4]

Yes tried but could not see what is expected. but it shows 3 record in final.

Am expecting 1 record in final that is max date with corresponding max value. i,e only yellow with 656

[qlikmsg4u]

what is expected output?

[#6]

[#7]

,Timestamp(Max([Updated On]),'MM/DD/YYYY hh:mm:ss TT'))

what is meant by TT in the format

[#8]

Any Suggestions Plz

[sasiparupudi1 · Accepted solution]

Try

[Project Metrics History]:

LOAD [Project ID] as ProjectID_SYS,

     [Project Name],

     trim([Metric Name]) as [Metric Name],

      Date(Date#(trim([Updated On]),'MMM DD, YYYY')) as [Updated On],

     [Value Before Update]

FROM

[Bk1.xlsx]

(ooxml, embedded labels, table is Sheet1);

Trans3:

NoConcatenate

Load

ProjectID_SYS,

[Updated On],

[Value Before Update] as N_Customer_Traction_Status

Resident [Project Metrics History]

where [Metric Name]='Customer Traction Status';

join

load

ProjectID_SYS,

Date(Max([Updated On]))  as Maxmetric

Resident [Project Metrics History]

where [Metric Name]='Customer Traction Status'

Group by ProjectID_SYS,[Metric Name];

Trans4:

NoConcatenate LOAD ProjectID_SYS,[Updated On],N_Customer_Traction_Status,Maxmetric Resident Trans3 where Maxmetric=[Updated On];

drop Table  [Project Metrics History];

drop Table Trans3;

I do not have your data to test but should work

[qlikmsg4u]

try this

[#11]

Thank you I got it.

But i have further things to do, the same with other metric name 'Customer Traction'.  this way i need to combine another 20 metrics. 

i replaced the metric name 'Customer Traction Status' to 'Customer Traction' in the above code. showing nulls.