topic Re: duplicates in QlikView

duplicates

Wed, 25 Nov 2020 16:16:04 GMT

Is it possible to only display duplicate SSN out of a file?

Re: duplicates

sunny_talwar — Fri, 22 Jul 2016 13:39:18 GMT

Do you mean during the load you want to only include those SSN which are duplicates?

Try this:

Table:

LOAD *

FROM Source;

Right Join (Table)

LOAD SSN

Where Count > 1;

LOAD SSN,

Count(SSN) as Count

Resident Table

Group By SSN;

Re: duplicates

jyothish8807 — Fri, 22 Jul 2016 13:45:28 GMT

Hi Robert,

You can do this at UI level also.Load all the data, since you may require it for other charts.

Create a straight chart.

Dimension: All the fields (Make sure suppress null value selected)

Expression:

If(Count(SSN)>1,SSN)

Regards

Re: duplicates

Fri, 22 Jul 2016 13:59:46 GMT

actually that is onlt the first check the next will be last name and first name and birthdate

Re: duplicates

Anonymous — Fri, 22 Jul 2016 14:08:34 GMT

Robert it would be better if you put a sample data together

Re: duplicates

Fri, 22 Jul 2016 14:13:10 GMT

Re: duplicates

jyothish8807 — Fri, 22 Jul 2016 14:14:15 GMT

Hi Robert,

You can create a new field which will be the combination of all the field you want to check:

eg:

Load *,

last name & first name & birthdate & SSN as NewField

from <>

Then at UI at expression,

if(Count(New Field >1, Your required field)

Regards

Re: duplicates

jyothish8807 — Fri, 22 Jul 2016 14:16:15 GMT

Hi Robert,

Try making this expression and not dimension.

Regards

Re: duplicates

Fri, 22 Jul 2016 14:21:04 GMT

please see sample

Re: duplicates

jyothish8807 — Fri, 22 Jul 2016 14:30:08 GMT

Load *,

rowno() as Sino,

autonumber(FacID & PatID & PatStatus & PatLName & PatFName & PatMI & Column1 & SSN & NsID & Floor & Room & Bed & BirthDate & AdmDate & DeathDate & DischargeDt) as New_field

From file.xlx;

if(Count(New Field >1, Your required field)

But QlikView will automatically identify duplicate entries. What is your actual requirement ?

Regards

Re: duplicates

Anonymous — Fri, 22 Jul 2016 14:32:09 GMT

Hi Rob,

Let me understand your requirement correctly.

1. We need to identify duplicate SSN's

2. For the duplicate SSN records we need to identify the duplicate names records. Is that correct or you are looking for something else. Please provide us the requirement in detail.

Re: duplicates

Fri, 22 Jul 2016 16:44:51 GMT

We have staff entering patient data and well they make a few keying errors so we can up with a few different checks to filter thru 78,000 accounts to locate them

First filter

SSN looking for duplicates and merge that patients if they are truly the same.

Second filter

But if the SSN was entered wrong then I need to match up on there name and maybe display birthdate to verify they are not just a totally different person.

I an open to suggestions?

I need to log off soon but will check back in 4 or 5 hours

I actually use the direct connect SQL

to get data but I run also in excel file to check my apps are linking correctly.