topic Re: Identifying anomalies in data in QlikView

Identifying anomalies in data

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

I'm looking for a simple way to identify incorrect values in my data.

I have two fields like this:

Name	ID
Ben	123
Ben	123
Sam	658
George	963
Alice	963

What I want to do is identify instances where the same ID has been used for more than one Name.

So in this example, George and Alice should be identified.

I'm happy to do this in front end or script, but can't think of a straightforward way to do it!!

Re: Identifying anomalies in data

prat1507 — Wed, 08 Nov 2017 11:08:41 GMT

Please see the desired app you're looking for, on clicking of the ID in the table you can see the Names assigned respectively.

Regards
Pratyush

Re: Identifying anomalies in data

Anil_Babu_Samineni — Wed, 08 Nov 2017 11:41:45 GMT

Perhaps this?

Count( {<Name = {"=Count(ID) <> 2 and Count(TOTAL <Name> ID) <> 2"}>} ID)

Re: Identifying anomalies in data

effinty2112 — Wed, 08 Nov 2017 13:37:45 GMT

Hi Jessica,

Try:

ID	Count(DISTINCT Name)	Concat(DISTINCT Name,',')
963	2	Alice,George

Where ID is the calculated dimension:

=Aggr(If(Count(DISTINCT Name) > 1,ID),ID)

check the option to Suppress When Value Is Null

Regards

Andrew

Re: Identifying anomalies in data

antoniotiman — Wed, 08 Nov 2017 14:25:54 GMT

In Script, may be like this

Table:
LOAD *,AutoNumber(RowNo(),ID) as ID1,Autonumber(RowNo(),Name) as Name1 Inline [
Name, ID
George, 963
Ben, 123
Ben, 123
Sam, 658
Alice, 963 ];
Left Join LOAD ID,1 as Flag
Resident Table
Where ID1 <> Name1 ;

Regards,

Antonio