Latest TimeStamp Record

[jpjust]

All,

I have query like this.

[Analyze]:

LOAD 
[appId] 
[PublishedAppName]
timestamp([createdDateTime]) as createdDateTime,
if([flagCount] =0,0,[flagCount]) as flagCount
RESIDENT RestConnectorMasterTable;

[App]:

ObjectId,
ObjectName as PublishedAppName,
Command,
LogTimeStamp
FROM [lib://QVD/command.QVD]

Here is my table

I want just one record to be shown, the last highlighted one (the latest logTimeStamp)

Is there a way this can be achieved on the script level or dashboard level?

Any help will be appreciated.

Thanks

[KGalloway]

You could right join your [App] table to a new table loaded from the [App] table that finds the max(LogTimeStamp) when grouping by the other fields. You would need to join on the fields you grouped by and the result of the max. This would filter out all the records that were not the latest timestamp for their group.

For example,

[App]:

ObjectId,
ObjectName as PublishedAppName,
Command,
LogTimeStamp
FROM [lib://QVD/command.QVD]

right join

[Maxes]:

load 

ObjectId,
PublishedAppName,
Command,
max(LogTimeStamp) as LogTimeStamp
resident [App]

group by ObjectId, PublishedAppName, Command;

[jpjust]

Thank you so much. I will give it a try.

[vinieme12]

either  of below will work

=FirstSortedValue(PublishedAppName,-LogTimeStamp)

OR

=MaxString({<LogTimeStamp={'$(=Timestamp(Max(LogTimeStamp)))'}>}PublishedAppName)

[jpjust]

Unfortunately, the issue still exists. Please see the join query at the bottom and please suggest.

So here is the result and expected result. 

Result
LogTimeStamp	Command	ObjectId	UserId	PublishedAppName
2023-01-30 19:21:16	Republish app	5100b382-de44-47fe-b383-9f2f191116a5	simionato	QSDA
2023-01-30 14:47:35	Republish app	6dd9233c-ec0b-42bb-a9b1-f929a254bf9f	justus	QSDA
2023-01-23 19:01:40	Republish app	afe7f2d7-917c-4913-870f-f5d1a7d38080	simionato	QSDA Collection
2023-01-19 15:27:08	Republish app	f6450dbc-61fb-4364-9ed4-1ee02e0790bd	justus	QSDA
2023-01-12 17:34:23	Republish app	71cdfd5e-d5c2-4be7-842d-c538b9b452f7	simionato	Command
2023-01-11 18:16:58	Republish app	7055b760-de08-4a2e-bbce-8f47d7036312	simionato	QSDA
Expected
LogTimeStamp	Command	ObjectId	UserId	PublishedAppName
2023-01-30 19:21:16	Republish app	5100b382-de44-47fe-b383-9f2f191116a5	simionato	QSDA
2023-01-23 19:01:40	Republish app	afe7f2d7-917c-4913-870f-f5d1a7d38080	simionato	QSDA Collection
2023-01-12 17:34:23	Republish app	71cdfd5e-d5c2-4be7-842d-c538b9b452f7	simionato	Command

And here is my query

[App]:
LOAD
"UserId",
ObjectId ,
ObjectName as PublishedAppName,
IF(Wildmatch(ObjectName,'*(1)*'),Replace(ObjectName,'(1)',''),
IF(Wildmatch(ObjectName,'*(2)*'),Replace(ObjectName,'(2)',''),
IF(Wildmatch(ObjectName,'*(3)*'),Replace(ObjectName,'(3)',''),
IF(Wildmatch(ObjectName,'*(4)*'),Replace(ObjectName,'(4)',''),
IF(Wildmatch(ObjectName,'*(5)*'),Replace(ObjectName,'(5)',''),ObjectName)))))as PublishedAppName,
Command,
LogTimeStamp
FROM [lib://QVD/GLOB/EU/Licenses/command.QVD]
(qvd) where Match (Command, 'Republish app', 'Publish app');

right join
[Max App]:
LOAD
"UserId",
ObjectId,
PublishedAppName,
Command,
max(LogTimeStamp) as LogTimeStamp
resident [App]
group by "UserId",
ObjectId,
PublishedAppName,
Command;

[KGalloway · Accepted solution]

The following adjusted code seems to work for me:

original:
load * inline [
LogTimeStamp, Command, ObjectId, UserId, ObjectName
2023-01-30 19:21:16, Republish app, 5100b382-de44-47fe-b383-9f2f191116a5, simionato, QSDA
2023-01-30 14:47:35, Republish app, 6dd9233c-ec0b-42bb-a9b1-f929a254bf9f, justus, QSDA
2023-01-23 19:01:40, Republish app, afe7f2d7-917c-4913-870f-f5d1a7d38080, simionato, QSDA Collection
2023-01-19 15:27:08, Republish app, f6450dbc-61fb-4364-9ed4-1ee02e0790bd, justus, QSDA
2023-01-12 17:34:23, Republish app, 71cdfd5e-d5c2-4be7-842d-c538b9b452f7, simionato, Command
2023-01-11 18:16:58, Republish app, 7055b760-de08-4a2e-bbce-8f47d7036312, simionato, QSDA

]
;

[App]:
LOAD
"UserId",
ObjectId ,
// ObjectName as PublishedAppName,
IF(Wildmatch(ObjectName,'*(1)*'),Replace(ObjectName,'(1)',''),
IF(Wildmatch(ObjectName,'*(2)*'),Replace(ObjectName,'(2)',''),
IF(Wildmatch(ObjectName,'*(3)*'),Replace(ObjectName,'(3)',''),
IF(Wildmatch(ObjectName,'*(4)*'),Replace(ObjectName,'(4)',''),
IF(Wildmatch(ObjectName,'*(5)*'),Replace(ObjectName,'(5)',''),ObjectName)))))as PublishedAppName,
Command,
LogTimeStamp
resident original
where Match (Command, 'Republish app', 'Publish app');

right join

[Max App]:
LOAD
PublishedAppName,
Command,
max(LogTimeStamp) as LogTimeStamp
resident [App]
group by PublishedAppName, Command
;

drop table original;

In the table that finds the max, the way you group is the key. If we also group by ObjectId and UserId, it could cause different versions of the same app to be grouped as different apps. This group by statement is what will determine how you want to define an app and its versions.

When I run the above load script, I get the following results (based on the dat aloaded in[original]):

If this doesn't work, you may also consider Vinieme12's suggestions above. 

[jpjust]

Excellent solution, it worked. Thank you so much.