Identify duplicates and mark them during load script

[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

[felipedl]

Hi,

It's been a while since i've posted something here, so i'm a little rusty.

Attached the sample app, spreadsheet used and code that is used to generate your outcome

tmp:
load
Passcode,
Status,
[Date Submitted]
Resident [Planilha1];



tmp_count_pass:
Load
Passcode,
count(Passcode) as Count
Resident tmp
group by
Passcode;

tmp_count_status:
Load
Passcode,
Status,
count(Status) as Count_Status
Resident tmp
group by
Passcode,
Status;

tmp_x:
load
Distinct
Passcode
Resident
tmp;

left join (tmp_x)
load
Passcode,
Count_Status as 'Partial'
Resident
tmp_count_status
where
Status = 'Partial';

left join (tmp_x)
load
Passcode,
Count_Status as 'Complete'
Resident
tmp_count_status
where
Status = 'Complete';

left join (tmp_x)
load
Passcode,
Count as [Row Count]
Resident tmp_count_pass;

left join (tmp_x)
Load
Passcode,
max([Date Submitted]) as [Max Status]
Resident
[Planilha1]
group by
Passcode;

left join (tmp_x)
Load
Passcode,
max([Date Submitted]) as [Max Complete]
Resident
[Planilha1]
where
Status = 'Complete'
group by
Passcode;

tmp_x_2:
Load
Passcode,
If
(
[Row Count] = 1,
alt([Max Status],[Max Complete]),
if
(
Test = 'Mark Latest Status',
[Max Status],
if
(
Test = 'Mark Latest Complete',
[Max Complete]
)
)
) as [Date For Join];
load
*,
If
(
[Row Count] = 1,
'Mark Row',
if
(
Partial = 2 and isnull(Complete)
or
Complete = 2 and isnull(Partial),
'Mark Latest Status',
'Mark Latest Complete'
)
) as [Test]

Resident
tmp_x;

left join ([Planilha1])
load
Passcode,
[Date For Join] as [Date Submitted],
'Y' as [Correct Record Flag]
Resident tmp_x_2;

drop table tmp_x_2;

drop tables tmp_x, tmp_count_pass,tmp_count_status,tmp;

NoConcatenate
Final_Data:
Load
[Response ID],
[Time Started],
[Date Submitted],
Status,
Passcode,
if (IsNull([Correct Record Flag]),'N',[Correct Record Flag]) as [Correct Record Flag]
Resident [Planilha1];

drop table [Planilha1];

Basically what I did were some checks to count how many lines are per passcode (field [Row Count]) and after that the count per passcode and status (to get the correct 'Partial' and 'Complete' status).

Last, i classify if it should be getting the last date per status or (when the lines are doubles and same status) or if should get the latest complete date.

Hope it helps,

Felipe.

Result looks like yours: