Compare Rows Where Certain Fields Match and Flag

[haymarketpaul]

QV12 SR3

I have one table that contains many duplicate emails (2 or more).

Based on certain defined rules i want to flag rows that fit each rule.  Best understood with an example i think:-

Let's say i have 2 rules (i actually have 15 rules):

1.  If Firstname & Last name & Email Match then Flag those rows with a field that says 'Rule1'

2.  If Firstname & Last name & WorkID Match then Flag those rows with a field that says 'Rule2'

Example Data:

Firstname	Lastname	Email	WorkID
Joe	Bloggs	jb@email.com	123456
Joe	Bloggs	jb@email.com	654321
Jane	Doe	jane@gmail.com	334455
Jane	Doe	doe@gmail.com	334455
Paul	Nox	nox@gmail.com	112233
Paul	Nox	nox@gmail.com	112233

So based on those 2 rules above the results would be:-

Firstname	Lastname	Email	WorkID	Rule1	Rule2
Joe	Bloggs	jb@email.com	123456	Yes
Joe	Bloggs	jb@email.com	654321	Yes
Jane	Doe	jane@gmail.com	334455		Yes
Jane	Doe	doe@gmail.com	334455		Yes
Paul	Nox	nox@gmail.com	112233	Yes	Yes
Paul	Nox	nox@gmail.com	112233	Yes	Yes

Having ordered the records correctly i tried using this sort of thing but can't flag the first row of each group (if that makes sense)

If(Email = Peek(Email) and LastName = Peek(LastName) and FirstName = Peek(FirstName),'Yes')    as [Rule 1?]

Can't help thinking there's a better way to do this.  Some king of self join maybe?  Or split the table into 2 and join on concatenated fields.

Hope that all makes sense

Any ideas much appreciated

[swuehl · Accepted solution]

Maybe like

INPUT:

LOAD Firstname, Lastname, Email, WorkID

FROM ...;

LEFT JOIN (INPUT)

LOAD Firstname, Lastname, Email,

        If(Count(Email)>1,'Yes') as Rule1

RESIDENT INPUT

GROUP BY Firstname, Lastname, Email;

//etc...

[vvira1316]

Hi,

very similar to what Stefan has suggested.

NoConcatenate
Data:
LOAD
ID
,Firstname
,Lastname
,Email
,WorkID
,Firstname & Lastname & Email as FN_LN_Email_Key
,Firstname & Lastname & WorkID as FN_LN_WorkID_Key
FROM
[..\Data\Test19.xlsx]
(ooxml, embedded labels, table is Sheet1);

NoConcatenate
EmailMatch:
LOAD
Firstname & Lastname & Email as FN_LN_Email_Key
,If(Count(DISTINCT ID)>1,'Yes','No') as Rule1
Resident Data
Group By Firstname, Lastname, Email;

NoConcatenate
WorkIDMatch:
LOAD
Firstname & Lastname & WorkID as FN_LN_WorkID_Key
,If(Count(DISTINCT ID)>1,'Yes','No') as Rule2
Resident Data
Group By Firstname, Lastname, WorkID;

[its_anandrjs]

Try this way

Tab1:

LOAD *,Firstname& Lastname& Email,Firstname& Lastname& WorkID,RowNo() as Row;

LOAD * INLINE [

    Firstname, Lastname, Email, WorkID

    Joe, Bloggs, jb@email.com, 123456

    Joe, Bloggs, jb@email.com, 654321

    Jane, Doe, jane@gmail.com, 334455

    Jane, Doe, doe@gmail.com, 334455

    Paul, Nox, nox@gmail.com, 112233

    Paul, Nox, nox@gmail.com, 112233

];

Left Join

LOAD *,if(Count1>1,'Yes') as Rule1;

LOAD Firstname& Lastname& Email, Count(WorkID) as Count1 Resident Tab1 Group By Firstname& Lastname& Email;

Left Join

LOAD *,if(Count2>1,'Yes') as Rule2;

LOAD Firstname& Lastname& WorkID, Count(Email) as Count2 Resident Tab1 Group By Firstname& Lastname& WorkID;

[haymarketpaul]

Thanks Everyone - working nicely now