Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Count Duplicate Records within a Date Range

Hi,

I have a long list of records and each record has a unique identifer (MRN); I need to identify how many records are duplicates based on the MRN within a 30 day range. So if someone was discharged on a certain date, I want to check if they were readmitted within 30 days of that discharge date.

Here is a sample of the records:

MRNAdmit DateDischarge DateDRG
1234512/1/201312/23/2013434
234561/3/20141/4/201423
325231/5/20141/10/2013255
452451/7/20141/10/201423
123451/8/20141/12/2014678

So record is 12345 is duplicate AND it falls within the 30 day date range, therefore I would like to count it; then on another table I would like to pull the MRN's and DRG's associated with the duplicate records... So the tables would display something like this:

YearMonthDuplicate Count
2013December0
2014January1

YearMonthMRNDRG
2013December12345434
2014January12345678

Thank you very much for all your help!

2 Replies
MVP
MVP

Re: Count Duplicate Records within a Date Range

Maybe like attached, creating the flags in the script and then simple charts to get your requested results.

SET DateFormat = 'M/D/YYYY';

INPUT:
LOAD MRN,
     [Admit Date],
     [Discharge Date],
     DRG
FROM
[https://community.qlik.com/thread/209958]
(html, codepage is 1252, embedded labels, table is @1);

TMP:
LOAD *,
  If(previous(MRN) = MRN and [Admit Date]-previous([Discharge Date]) <=30, 1) as DuplicateFlag
Resident INPUT
ORDER BY MRN, [Admit Date] asc;

RESULT:
LOAD *,
  Month([Admit Date]) as Month,
  Year([Admit Date]) as Year,
  If(previous(MRN) = MRN and Previous(DuplicateFlag),1) as DuplicateTriggerFlag
Resident TMP
ORDER BY MRN, [Admit Date] desc;

DROP TABLE INPUT, TMP;

amayuresh
Contributor III

Re: Count Duplicate Records within a Date Range

Script:

t:

LOAD * INLINE [

    MRN, ADMIT DATE, DISCHARGE DATE, DRG

    12345, 12/1/2013, 12/23/2013, 434

    23456, 1/3/2014, 1/4/2014, 23

    32523, 1/5/2014, 1/10/2013, 255

    45245, 1/7/2014, 1/10/2014, 23

    12345, 1/8/2014, 1/12/2014, 678

];

t1:

load num(MRN) as MRN, [ADMIT DATE], [DISCHARGE DATE], DRG,right([ADMIT DATE],4) as AdmitYear, pick(mid([ADMIT DATE],1,(index([ADMIT DATE],'/')-1)),'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec') as AdmitMonth, right([ADMIT DATE],4)&Month([ADMIT DATE]) as YearMonth

Resident t;

drop table t;

Output:

1.PNG

1st Table:

Dimensions: AdmitYear, AdmitMonth

Expression= =if(count(YearMonth)>1,1,0)

2nd Table:

Dimensions: AdmitYear, AdmitMonth, MRN

Expression= =Aggr(Max(DRG),YearMonth)

Community Browser