topic Re: Latest TimeStamp Record in App Development

Latest TimeStamp Record

jpjust — Mon, 23 Jan 2023 20:30:59 GMT

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

Re: Latest TimeStamp Record

KGalloway — Mon, 23 Jan 2023 22:12:39 GMT

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;

Re: Latest TimeStamp Record

jpjust — Tue, 24 Jan 2023 21:36:58 GMT

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

Re: Latest TimeStamp Record

vinieme12 — Fri, 27 Jan 2023 03:10:43 GMT

either of below will work

=FirstSortedValue(PublishedAppName,-LogTimeStamp)

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

Re: Latest TimeStamp Record

jpjust — Tue, 31 Jan 2023 16:28:18 GMT

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;

Re: Latest TimeStamp Record

KGalloway — Tue, 31 Jan 2023 21:28:08 GMT

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.

Re: Latest TimeStamp Record

jpjust — Wed, 01 Feb 2023 19:41:11 GMT

Excellent solution, it worked. Thank you so much.