topic Re: Map duplicates in QlikView

Map duplicates

Mon, 06 Feb 2017 17:03:32 GMT

I am trying to match drug label names, but it looks like I am missing something?

Drug Label Name is ProdDescAbbr,

The end result should show only duplicate drug label names.

LIB CONNECT TO 'pharmoresql production (pharmore_rwinkel)';

LOAD NDC,
    ProdDescAbbr,
    DrugNameCd;
SQL SELECT NDC,
    ProdDescAbbr,
    DrugNameCd
FROM Drug.dbo.ManufNames;

Join
Map_Duplicate:

// Mapping load

Load

ProdDescAbbr,
//sum(Flag) as Frq,
If(Count(ProdDescAbbr)>1, 'Yes', 'No') as DuplicateName
Resident [ManufNames] Group by ProdDescAbbr;

Re: Map duplicates

Anonymous — Mon, 06 Feb 2017 17:24:11 GMT

You have to filter the data in the where clause; You only have added to the data model if it is duplicate or not. to show only the duplicates you need to filter the data accordingly. Or maybe is not the entire script.

Add:

ManufNames:

load *

resident ManufNames

where DuplicateName='Yes';

Re: Map duplicates

Mon, 06 Feb 2017 17:31:53 GMT

It is that it puts a Yes when they are not the exact same like it is only matching up on part of the name. The ACID REDUCER TAN line should be no. Its like it just matches the full drug name

Re: Map duplicates

Anonymous — Mon, 06 Feb 2017 17:40:39 GMT

put only the first part, and Select field ProdDescAbbr, and show frequency. Or make a table with ProdDescAbbr, and expression count(1). You will have there the duplicates if you sort by count expresion.

LIB CONNECT TO 'pharmoresql production (pharmore_rwinkel)';

LOAD NDC,
    ProdDescAbbr,
    DrugNameCd;
SQL SELECT NDC,
    ProdDescAbbr,
    DrugNameCd
FROM Drug.dbo.ManufNames;

If there the frequency is more than 1 or count more than 1, you have duplicates,matches the full drug name.