Flag Latest among duplicate rows

[jiwaniakbiar]

This is the table I am working with (Correct Record Flag) is not included in the table. I want to add this during load script.

I want to implement logic to mark Correct Record Flag when there are more than one rows with same passcode

1. If there is only one row with the passcode, mark this flag as "Y" (example: zz2832s, tu28cqc)

2. If there are more than one rows then following are the scenarios

- If there are two rows -One with Status 'Partial' and other 'Complete' then mark row with 'Complete' as "Y" and "Partial" as "N" (example: 55fez86)

- If there are two rows both with Same status i.e. both Partial or both Complete, then mark one with lastest based on Date Submitted  (Example: 9928cos)

- if there are 3 or more rows with combination of status, then pick the latest "Complete" and markt it as "Y" (Example: 1zr5mjc)

Response ID	Time Started	Date Submitted	Status	Passcode	Correct Record Flag
1	20/02/2019 11:04	20/02/2019 21:34	Partial	1zr5mjc	N
2	20/02/2019 11:51	20/02/2019 22:22	Complete	1zr5mjc	Y
3	20/02/2019 21:00	21/02/2019 7:33	Partial	1zr5mjc	N
4	21/02/2019 0:56	21/02/2019 11:27	Partial	55fez86	N
5	21/02/2019 0:55	21/02/2019 11:34	Complete	55fez86	N
6	21/02/2019 3:04	21/02/2019 13:35	Complete	55fez86	Y
7	20/02/2019 11:53	20/02/2019 22:24	Partial	9928cos	N
8	20/02/2019 11:53	20/02/2019 23:24	Partial	9928cos	Y
9	20/02/2019 11:53	21/02/2019 21:24	Partial	tu28cqc	Y
10	20/02/2019 11:53	22/02/2019 23:34	Complete	zz2832s	Y

[sunny_talwar · Accepted solution]

Try this

Table:
LOAD * INLINE [
    Response ID, Time Started, Date Submitted, Status, Passcode
    1, 20/02/2019 11:04, 20/02/2019 21:34, Partial, 1zr5mjc
    2, 20/02/2019 11:51, 20/02/2019 22:22, Complete, 1zr5mjc
    3, 20/02/2019 21:00, 21/02/2019 7:33, Partial, 1zr5mjc
    4, 21/02/2019 0:56, 21/02/2019 11:27, Partial, 55fez86
    5, 21/02/2019 0:55, 21/02/2019 11:34, Complete, 55fez86
    6, 21/02/2019 3:04, 21/02/2019 13:35, Complete, 55fez86
    7, 20/02/2019 11:53, 20/02/2019 22:24, Partial, 9928cos
    8, 20/02/2019 11:53, 20/02/2019 23:24, Partial, 9928cos
    9, 20/02/2019 11:53, 21/02/2019 21:24, Partial, tu28cqc
    10, 20/02/2019 11:53, 22/02/2019 23:34, Complete, zz2832s
];

FinalTable:
LOAD *,
	 If(Passcode = Previous(Passcode), 'N', 'Y') as [Correct Record Flag]
Resident Table
Order By Passcode, Status, [Date Submitted] desc;

DROP Table Table;

[jiwaniakbiar]

Perfect. This works. Thanks Tarun for your help

[sunny_talwar]

---

@jiwaniakbiar wrote:

Thanks Tarun for your help

---

Tarun?