Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Conditional Formatting

In excel I can do conditional formatting as displayed below.  Is it possible to do the same thing in Qlik sense?

I only want to display duplicates because they are errors and I need to fix them in my system.

1 Solution

Accepted Solutions
NickHoff
Specialist
Specialist

datatable would be your existing load.

Join
Map_Duplicate:

//Mapping load

Load

InvoiceNum,
//sum(Flag) as Frq,
If(Count(InvoiceNum)>1, 'Yes', 'No') as Duplicate
Resident datatable Group by  InvoiceNum;


//data2:
//load *,
//if(ApplyMap('Map_Duplicate',InvoiceNum)>1,'Yes', 'No') as Duplicate
//
//
//Resident datatable;
//
//drop Table datatable;

View solution in original post

8 Replies
NickHoff
Specialist
Specialist

I'd create a flag in my data model that indicates if there is a duplicate then throw together a set analysis statement {$<DuplicateIND = {'Y'}>} to only display the data that has duplicates.

Not applicable
Author

how do I create the flag?

NickHoff
Specialist
Specialist

datatable would be your existing load.

Join
Map_Duplicate:

//Mapping load

Load

InvoiceNum,
//sum(Flag) as Frq,
If(Count(InvoiceNum)>1, 'Yes', 'No') as Duplicate
Resident datatable Group by  InvoiceNum;


//data2:
//load *,
//if(ApplyMap('Map_Duplicate',InvoiceNum)>1,'Yes', 'No') as Duplicate
//
//
//Resident datatable;
//
//drop Table datatable;

View solution in original post

Not applicable
Author

Now I need to filter out all the duplicate PatID s

Patients:

LIB CONNECT TO [pharmoresql production (pharmore_rwinkel)];

LOAD [FacID],

DISTINCT[PatID],

[PatLName],

[PatFName],

[Floor],

[NsID],

[Room],

[PatStatus],

[SSN],

[BirthDate],

[AdmDate],

[PatMI],

[DischargeDt],

[Bed],

[DefProfileOnly],

[DeathDate],

[StatusNameCd];

SQL SELECT  "FacID",

  "PatID",

  "PatLName",

  "PatFName

NickHoff
Specialist
Specialist

Add a flag for multiple PatID's and then apply the same logic above with the set pointing to {$<DupePatIDFlag =  {'N'}>}

Not applicable
Author

If there is a duplicate I need to see one of them.  that's why I was trying to use the distinct keywork

NickHoff
Specialist
Specialist

You still would see the duplicates.  Distinct wouldn't help your case because you'd be limiting your data to your group by.  Using the flag is the way to go on this one.

Not applicable
Author

in the data I pulled a patid will have duplicates that are ok  when a patient moves to a new facility they are transferred some I can have a true duplicate meaning I could have five patids the same but all pointing to the correct AR account.  PatId = AR account number.

so what my logic is remove all duplicate PatId s then search for duplicate SSN s  and all duplicates will be the same patient but with two AR account numbers.

need to limit the false duplicate SSN