Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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.

Tags (1)
1 Solution

Accepted Solutions
qvelhoff
Valued Contributor

Re: Conditional Formatting

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;

8 Replies
qvelhoff
Valued Contributor

Re: Conditional Formatting

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

Re: Conditional Formatting

how do I create the flag?

qvelhoff
Valued Contributor

Re: Conditional Formatting

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;

Not applicable

Re: Conditional Formatting

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

qvelhoff
Valued Contributor

Re: Conditional Formatting

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

Not applicable

Re: Conditional Formatting

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

qvelhoff
Valued Contributor

Re: Conditional Formatting

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

Re: Conditional Formatting

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