topic Count Duplicate Records within a Date Range in QlikView

Count Duplicate Records within a Date Range

Thu, 17 Mar 2016 16:12:43 GMT

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:

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

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:

Year	Month	Duplicate Count
2013	December	0
2014	January	1

Year	Month	MRN	DRG
2013	December	12345	434
2014	January	12345	678

Thank you very much for all your help!

Re: Count Duplicate Records within a Date Range

swuehl — Thu, 17 Mar 2016 18:18:04 GMT

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;

Re: Count Duplicate Records within a Date Range

amayuresh — Fri, 18 Mar 2016 04:02:21 GMT

Script:

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:

1st Table:

Dimensions: AdmitYear, AdmitMonth

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

2nd Table:

Dimensions: AdmitYear, AdmitMonth, MRN

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