Fetch latest records

[Varun1]

Hi,

My data currently looks like this.

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}	6	2	5	Pending	0 00:20:21	Less Than 7 Days	12/05/2021 10:06:20 AM
{E075B973-0000-C511-B2FA-0428B188370D}	6	1	5	Pending	0	Less Than 7 Days	12/05/2021 10:05:59 AM
{E075B973-0000-C511-B2FA-0428B188370D}	5	2	2	Complete	0 00:15:18	Less Than 7 Days	21/10/2020 10:21:15 AM
{E075B973-0000-C511-B2FA-0428B188370D}	5	1	2	Pending	0	Less Than 7 Days	21/10/2020 10:05:57 AM
{E075B973-0000-C511-B2FA-0428B188370D}	4	2	2	Complete	0 00:07:21	Less Than 7 Days	20/10/2020 2:58:41 PM
{E075B973-0000-C511-B2FA-0428B188370D}	4	1	2	Pending	0	Less Than 7 Days	20/10/2020 2:51:20 PM
{E075B973-0000-C511-B2FA-0428B188370D}	3	2	2	Complete	0 03:22:03	Less Than 7 Days	20/10/2020 2:37:09 PM
{E075B973-0000-C511-B2FA-0428B188370D}	3	1	2	Pending	0	Less Than 7 Days	20/10/2020 11:15:06 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

In the data i have multiple case identifiers corresponding to which there are task cycles that get completed only after the Stage number = to the max stages in the cycle.

What i need to do now is to remove the rows where the status is "pending"and keep only the rows with the "Complete" status in the previous cycles i.e. cycles prior to 6 . As shown below.

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}	6	2	5	Pending	0 00:20:21	Less Than 7 Days	12/05/2021 10:06:20 AM
{E075B973-0000-C511-B2FA-0428B188370D}	6	1	5	Pending	0	Less Than 7 Days	12/05/2021 10:05:59 AM
{E075B973-0000-C511-B2FA-0428B188370D}	5	2	2	Complete	0 00:15:18	Less Than 7 Days	21/10/2020 10:21:15 AM
{E075B973-0000-C511-B2FA-0428B188370D}	4	2	2	Complete	0 00:07:21	Less Than 7 Days	20/10/2020 2:58:41 PM
{E075B973-0000-C511-B2FA-0428B188370D}	3	2	2	Complete	0 03:22:03	Less Than 7 Days	20/10/2020 2:37:09 PM
{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}	1	2	2	Complete	0 04:24:59	Less Than 7 Days	04/08/2020 10:38:39 PM

Please advice on how to achieve this?

Thanks,

Varun

[Gysbert_Wassenaar · Accepted solution]

Perhaps like this:

Temp1:
LOAD * FROM ...source...;

Data:
NOCONCATENATE LOAD *, Cycle as CycleLookUp RESIDENT Temp1 WHERE [Overall Status] = 'Complete';

Temp2:
NOCONCATENATE LOAD * RESIDENT Temp1 WHERE NOT Exists(CycleLookUp,Cycle);

INNER JOIN (Temp2) LOAD max(Cycle) as Cycle RESIDENT Temp2;

CONCATENATE (Data)
LOAD * RESIDENT Temp2;

DROP TABLE Temp1, Temp2;

DROP FIELD CycleLookUp;

[Varun1]

Hi Gysbert,

Thank you for your suggestion however, the output i am getting from your suggestion does not include the latest cycle i.e. Cycle 6. Please let me know how i can add that so i can get the output as mentioned before.

Thanks in advance.

[Gysbert_Wassenaar]

WFM.

[Varun1]

Sorry my bad.

Thanks a ton for the solution