Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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;
Thank you so much. I will give it a try.
either of below will work
=FirstSortedValue(PublishedAppName,-LogTimeStamp)
OR
=MaxString({<LogTimeStamp={'$(=Timestamp(Max(LogTimeStamp)))'}>}PublishedAppName)
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;
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.
Excellent solution, it worked. Thank you so much.