topic Compare Rows Where Certain Fields Match and Flag in QlikView

Compare Rows Where Certain Fields Match and Flag

haymarketpaul — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Compare Rows Where Certain Fields Match and Flag

swuehl — Mon, 09 Oct 2017 15:59:56 GMT

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...

Re: Compare Rows Where Certain Fields Match and Flag

vvira1316 — Mon, 09 Oct 2017 16:05:34 GMT

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;

Re: Compare Rows Where Certain Fields Match and Flag

its_anandrjs — Mon, 09 Oct 2017 16:14:42 GMT

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

];

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;

Re: Compare Rows Where Certain Fields Match and Flag

haymarketpaul — Tue, 10 Oct 2017 09:23:34 GMT

Thanks Everyone - working nicely now