Display only the latest entries of a Case ID

[Varun1]

Hi,

What I require to do is show the workflow of the latest records of a 'Case ID'. In the data if the Stage number is equal to the max  stage then the status will show complete. I have attached the required output.

Sample data:

Case Identifier	Cycle	Stage	Max stages in the cycle	Overall Status	Time (Days and Hours)	SLA Time Scale	SLA.Time
{E075B973-0000-C511-B2FA-0428B188370D}	3	2	3	Pending	0 00:46:35	Less Than 7 Days	05/09/2020 11:28:05 AM
{E075B973-0000-C511-B2FA-0428B188370D}	3	1	3	Pending	0	Less Than 7 Days	05/09/2020 10:41:30 AM
{E075B973-0000-C511-B2FA-0428B188370D}	2	2	2	Complete	0 00:46:35	Less Than 7 Days	05/08/2020 11:28:05 AM
{E075B973-0000-C511-B2FA-0428B188370D}	2	1	2	Pending	0	Less Than 7 Days	05/08/2020 10:41:30 AM
{E075B973-0000-C511-B2FA-0428B188370D}	1	2	2	Complete	0 04:24:59	Less Than 7 Days	04/08/2020 10:38:39 PM
{E075B973-0000-C511-B2FA-0428B188370D}	1	1	2	Pending	0	Less Than 7 Days	04/08/2020 6:13:40 PM
{1234B}	1	2	3	Pending	0 04:24:59	Less Than 7 Days	04/08/2020 10:38:39 PM
{1234B}	1	1	3	Pending	0	Less Than 7 Days	04/08/2020 6:13:40 PM

Please provide a solution for the same.

Thanks in advance

[MayilVahanan · Accepted solution]

Hi @Varun1 

Try like below

Temp:
LOAD * INLINE [
Case Identifier, Cycle, Stage, Max stages in the cycle, Overall Status, Time (Days and Hours), SLA Time Scale, SLA.Time
{E075B973-0000-C511-B2FA-0428B188370D}, 3, 2, 3, Pending, 0 00:46:35, Less Than 7 Days, 05/09/2020 11:28:05 AM
{E075B973-0000-C511-B2FA-0428B188370D}, 3, 1, 3, Pending, 0, Less Than 7 Days, 05/09/2020 10:41:30 AM
{E075B973-0000-C511-B2FA-0428B188370D}, 2, 2, 2, Complete, 0 00:46:35, Less Than 7 Days, 05/08/2020 11:28:05 AM
{E075B973-0000-C511-B2FA-0428B188370D}, 2, 1, 2, Pending, 0, Less Than 7 Days, 05/08/2020 10:41:30 AM
{E075B973-0000-C511-B2FA-0428B188370D}, 1, 2, 2, Complete, 0 04:24:59, Less Than 7 Days, 04/08/2020 10:38:39 PM
{E075B973-0000-C511-B2FA-0428B188370D}, 1, 1, 2, Pending, 0, Less Than 7 Days, 04/08/2020 6:13:40 PM
{1234B}, 1, 2, 3, Pending, 0 04:24:59, Less Than 7 Days, 04/08/2020 10:38:39 PM
{1234B}, 1, 1, 3, Pending, 0, Less Than 7 Days, 04/08/2020 6:13:40 PM
];

Final:

Load [Case Identifier]&'|'& Cycle as Key, * Resident Temp where [Overall Status] = 'Complete';

Concatenate
Load * Resident Temp where not Exists(Key, [Case Identifier]&'|'& Cycle);

DROP Field Key;

DROP Table Temp;

[Varun1]

Perfect Thanks a ton!!! 😁

Regards,

Varun