2 Replies Latest reply: Mar 18, 2016 12:02 AM by Mayuresh Athalye RSS

    Count Duplicate Records within a Date Range

    Raj Sharma

      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!

        • Re: Count Duplicate Records within a Date Range
          Stefan Wühl

          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
            Mayuresh Athalye

            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)