topic Re: Want most recent record for each day (date) for multiple projects in App Development

Want most recent record for each day (date) for multiple projects

erieqlik — Tue, 14 May 2024 16:46:03 GMT

I have a history table with the following: project name, status, create date (of status), and a bunch of other fields.

I want the most recent record for each project name & project type.

Data:

Load * inline [

project name, status, create date

Alpha, Backlog, 2/16/2024 1:41:31 PM

Alpha, Approved, 2/16/2024 1:45:13 PM

Alpha, In Progress, 2/16/2024 1:57:45 PM

Alpha, Completed, 2/19/2024 3:45:33 PM

Beta, Backlog, 2/1/2024 9:01:01 AM

Beta, In Progress, 2/1/2024 4:01:54 PM

Beta, Backlog, 2/7/2024 8:02:12 AM

Beta, Approved, 2/12/2024 12:10:17 PM

Beta, Completed, 2/12/2024 2:44:59 PM

];

My result table should show just these data points…

Alpha, In Progress, 2/16/2024 1:57:45 PM <- this was the last thing that happened on 2/16

Alpha, Completed, 2/19/2024 3:45:33 PM <- this was the only thing that happened on 2/19

Beta, In Progress, 2/1/2024 4:01:54 PM <- this was the last thing that happened on 2/1

Beta, Backlog, 2/7/2024 8:02:12 AM <- this was the only thing that happened on 2/7

Beta, Completed, 2/12/2024 2:44:59 PM <- this was the last thing that happened on 2/12

My actual data has many more columns – how would I preserve that data?

Re: Want most recent record for each day (date) for multiple projects

igoralcantara — Tue, 14 May 2024 17:21:10 GMT

Try this:

Data:

Load * inline [

project name, status, create date

Alpha, Backlog, 2/16/2024 1:41:31 PM

Alpha, Approved, 2/16/2024 1:45:13 PM

Alpha, In Progress, 2/16/2024 1:57:45 PM

Alpha, Completed, 2/19/2024 3:45:33 PM

Beta, Backlog, 2/1/2024 9:01:01 AM

Beta, In Progress, 2/1/2024 4:01:54 PM

Beta, Backlog, 2/7/2024 8:02:12 AM

Beta, Approved, 2/12/2024 12:10:17 PM

Beta, Completed, 2/12/2024 2:44:59 PM

];

Summary:

Load

[project name],

status,

Timestamp(Min([create date])) As [First Occurance]

Resident Data

Group By [project name], status;

Re: Want most recent record for each day (date) for multiple projects

erieqlik — Tue, 14 May 2024 17:46:32 GMT

This is closer but not there yet. A story/project can go through a series of events (status changes) in a day. I want the last thing that happened to a project on any given day. If status changes 3 times in a day:

backlog to review

review to in progress

in progress to completed

The above would have a single record for the last status of completed.

Re: Want most recent record for each day (date) for multiple projects

igoralcantara — Tue, 14 May 2024 17:49:14 GMT

So, you want for each status in a project the last update in each date or the last status update for a record?

Re: Want most recent record for each day (date) for multiple projects

igoralcantara — Tue, 14 May 2024 17:55:19 GMT

Let's try this then:

Data:
Load * inline [
project name, status, create date
Alpha, Backlog, 2/16/2024 1:41:31 PM
Alpha, Approved, 2/16/2024 1:45:13 PM
Alpha, In Progress, 2/16/2024 1:57:45 PM
Alpha, Completed, 2/19/2024 3:45:33 PM
Beta, Backlog, 2/1/2024 9:01:01 AM
Beta, In Progress, 2/1/2024 4:01:54 PM
Beta, Backlog, 2/7/2024 8:02:12 AM
Beta, Approved, 2/12/2024 12:10:17 PM
Beta, Completed, 2/12/2024 2:44:59 PM
];

Summary:
Load
[project name],
Timestamp(Max([create date])) As [Last Status Change]
Resident Data
Group By [project name], status;

Left Join(Summary)
Load
[project name],
[create date] As [Last Status Change],
status As [Last Status]
Resident Data;

Re: Want most recent record for each day (date) for multiple projects

erieqlik — Tue, 14 May 2024 18:39:27 GMT

I just want these records from data:

Re: Want most recent record for each day (date) for multiple projects

igoralcantara — Tue, 14 May 2024 18:43:38 GMT

Cool. Let's try this now:

Data:
Load *
, Date(Floor([create date])) As CreateDateOnly
;
Load * inline [
project name, status, create date
Alpha, Backlog, 2/16/2024 1:41:31 PM
Alpha, Approved, 2/16/2024 1:45:13 PM
Alpha, In Progress, 2/16/2024 1:57:45 PM
Alpha, Completed, 2/19/2024 3:45:33 PM
Beta, Backlog, 2/1/2024 9:01:01 AM
Beta, In Progress, 2/1/2024 4:01:54 PM
Beta, Backlog, 2/7/2024 8:02:12 AM
Beta, Approved, 2/12/2024 12:10:17 PM
Beta, Completed, 2/12/2024 2:44:59 PM
];

Summary:
Load
[project name],
CreateDateOnly,
Timestamp(Max([create date])) As [Last Status Change]
Resident Data
Group By [project name], CreateDateOnly;

Left Join(Summary)
Load
[project name],
CreateDateOnly,
[create date] As [Last Status Change],
status As [Last Status]
Resident Data;

Droop Field CreateDateOnly;

Re: Want most recent record for each day (date) for multiple projects

erieqlik — Tue, 14 May 2024 18:45:02 GMT

I think I get it. I need to select and then join my results. I just need to determine how.

Re: Want most recent record for each day (date) for multiple projects

igoralcantara — Tue, 14 May 2024 18:45:59 GMT

Check my last code. I think this is it.

Re: Want most recent record for each day (date) for multiple projects

erieqlik — Tue, 14 May 2024 19:51:51 GMT

I should have asked... What does this mean/do? How does it work? It appears to be part of the 'Data' table but I do not see it in the data model viewer.

Re: Want most recent record for each day (date) for multiple projects

igoralcantara — Tue, 14 May 2024 19:58:16 GMT

Your Create Date is not a date field but a timestamp. In order to get the last event in each day, I need a field that is just a date, without the time. All date/time values in Qlik are numbers. Decimals represent the time and integer represent the days. So, I round it (Floor) to remove the time and get just the date.

You don't see it in the data model because I drop it in line 36. Comment that line and you will see it.