Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Finding one Value of with Max Date

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];


3.PNG

1 Solution

Accepted Solutions
sasiparupudi1
Master III
Master III

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

View solution in original post

21 Replies
senpradip007
Specialist III
Specialist III

Try like

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

Resident [Project Metrics History]
where [Metric Name]='Customer Traction Status'

Group by ProjectID_SYS,[Metric Name];

Not applicable
Author

Thanks pradip,

but the result is still showing 2 values .4.PNG

For check the actual table before the transformation used above with Trans3 and Trans4. the date is this way below.

5.PNG

Not applicable
Author

It is very usefull

senpradip007
Specialist III
Specialist III

Can you upload sample qvw ?

Not applicable
Author

Please find the attachment.

qlikmsg4u
Specialist
Specialist

try this

Trans4:

load

ProjectID_SYS,

if([Metric Name]='Customer Traction Status',Max(Timestamp#([Updated On],'MMM,DD YYYY'))) as Maxmetric

Resident [Project Metrics History]

where [Metric Name]='Customer Traction Status'

Group by ProjectID_SYS,[Metric Name];

senpradip007
Specialist III
Specialist III

Reload this attachment and let me know.

qlikmsg4u
Specialist
Specialist

Vamsi,

Find the attached one, Hope this will help to you

Not applicable
Author

Its not working Pradip