10 Replies Latest reply: May 10, 2018 11:18 AM by VIJAY VIRA RSS

    How to avoid duplicate values

    Ash Patel

      I am getting repeated values on each row of the column. Below is the table. There are only 2 distinct value for Code status but it creates duplicate in column code. Also, for Primary code there is only one distinct code but it gets repeated on each row. Any idea to resolve this issue. Thank you

         

      IDCodeCode_StatusPrimary Code
      4P07.03CPR-Full CodeSTEMI
      4P07.03No CPRSTEMI
      4C67.9CPR-Full CodeSTEMI
      4C67.9No CPRSTEMI
      4C68.9CPR-Full CodeSTEMI
      4C68.9No CPRSTEMI
      4D50.9CPR-Full CodeSTEMI
      4D50.9No CPRSTEMI
      4E21.3CPR-Full CodeSTEMI
      4E21.3No CPRSTEMI
      5E78.5CPR-Full CodeVTE
      5E78.5No CPRVTE
      5E86.1CPR-Full CodeVTE
      5E86.1No CPRVTE
      5G89.3CPR-Full CodeVTE
      5G89.3No CPRVTE
      5J21.9CPR-Full CodeVTE
      5J21.9No CPRVTE
      5P29.12CPR-Full CodeVTE
      5P29.12No CPRVTE
        • Re: How to avoid duplicate values
          VIJAY VIRA

          Is this your raw data or data in the chart? Would it be possible for you to share sample data and expected output?

            • Re: How to avoid duplicate values
              Ash Patel

              The data are in chart. They are in list box since it has been extracted. Data are huge with age, gender, birth date, etc.

              Output I am looking for

                 

               

              ID            Code            Code_Status            Primary Code

              4              P07.03           CPR-Full Code        STEMI

                                                     No CPR 

                              C67.9 

                              C68.9

                               D50.9

                               E21.3

              5              E78.5            CPR-Full Code         VTE

                                                    No CPR

                              E86.1

                              G89.3

                              J21.9  

                              P29.12       

                • Re: How to avoid duplicate values
                  VIJAY VIRA

                  I'm not getting what you mean by it s in list box. If it is huge you can take few row of data and mask any sensitive data if there is any.

                   

                  Would it be possible for you to share a sample of source data without any transformation or processing.

                    • Re: How to avoid duplicate values
                      Ash Patel

                        Attach are my source data  

                      IDAgeNameClassLOSUnitDepartmentGenderCodeROMSOICode_StatusPrimary_code
                      474SMITHIN1NEURO1FE11.921CPR - Full CodeS06.6X0A- Traumatic subarachnoid hemorrhage without loss of consciousness, initial encounter
                      No CPR - Intubate
                      K76.8921
                      R40.243011
                      S42.302A11
                      W10.9XXA11
                      Y92.911
                      Z6611
                      Z79.411
                      D68.922
                      G93.8922
                      S06.5X0A55
                      S02.119A66
                      S06.1X0A66
                      S06.6X0A66
                      562RHONDAIN7NEURO1FB18.211CPR - Full CodeS06.6X0A- Traumatic subarachnoid hemorrhage without loss of consciousness, initial encounter
                      E16.211
                      F11.1011
                      F14.1011
                      F17.21011
                      F41.811
                      G89.1811
                      G93.211
                      I1011
                      R09.0211
                      R40.241011
                      S42.022A11
                      W17.89XA11
                      670DONNAIN2MED A5FE78.511CPR - Full CodeA41.9- Sepsis, unspecified organism
                      F32.911No CPR - Intubate
                      G89.2911
                      H40.911
                      I25.1011
                      K52.911
                      K59.0011
                      M19.04111
                      M19.04211
                      M54.911
                      R09.0211
                      Z79.0111
                      Z85.311
                      Z86.71811
                      Z87.89111
                      Z88.811
                      Z96.64111
                      Z96.65311
                      Z98.4111
                      Z98.4211
                      755OWENIN1MED A5MD64.911CPR - Full CodeJ96.01- Acute respiratory failure with hypoxia
                      No CPR - Palliative and Supportive Care
                      E78.0011
                      F10.1011
                      F17.20011
                      F31.911
                      F41.011
                      F41.911
                      F43.1011
                      G47.0011
                      I12.911
                      K21.911
                      N18.911
                      X58.XXXA11
                      Y90.011
                      Y92.911
                      Z51.511
                      Z6611
                      889LEONAIN1CARD9FE78.511No CPR - Palliative and Supportive CareI21.09- ST elevation (STEMI) myocardial infarction involving other coronary artery of anterior wall
                      F03.9021
                      I1011
                      I25.1011
                      I45.1021
                      M19.9011
                      Z51.511
                      Z6611
                      562RHONDAIN7NEURO1FE87.512CPR - Full CodeS06.6X0A- Traumatic subarachnoid hemorrhage without loss of consciousness, initial encounter
                      FG81.9412
                      FI72.022
                      FI95.922
                      FS22.42XA12
                      FT79.7XXA22
                      FT80.818A12
                      670DONNAIN2MED A5FD68.922CPR - Full CodeA41.9- Sepsis, unspecified organism
                      No CPR - Intubate
                      E66.212No CPR - IntubateA41.9- Sepsis, unspecified organism
                      E86.112No CPR - IntubateA41.9- Sepsis, unspecified organism
                      G62.922CPR - Full CodeA41.9- Sepsis, unspecified organism
                      I11.022No CPR - IntubateA41.9- Sepsis, unspecified organism
                      I25.512No CPR - IntubateA41.9- Sepsis, unspecified organism
                      I48.022No CPR - IntubateA41.9- Sepsis, unspecified organism
                      Z68.4112No CPR - IntubateA41.9- Sepsis, unspecified organism
                      Z99.8122No CPR - IntubateA41.9- Sepsis, unspecified organism
                      755OWENIN1MED A5ME87.222No CPR - Palliative and Supportive CareJ96.01- Acute respiratory failure with hypoxia
                      G25.312CPR - Full Code
                      889LEONAIN1CARD9FI95.932No CPR - Palliative and Supportive CareI21.09- ST elevation (STEMI) myocardial infarction involving other coronary artery of anterior wall
                      562RHONDAIN7NEURO1FE87.423CPR - Full CodeS06.6X0A- Traumatic subarachnoid hemorrhage without loss of consciousness, initial encounter
                      562RHONDAIN7NEURO1FG91.323
                      562RHONDAIN7NEURO1FJ44.913
                      562RHONDAIN7NEURO1FS27.2XXA23
                      670DONNAIN2MED A5FI50.2333CPR - Full CodeA41.9- Sepsis, unspecified organism
                      No CPR - Intubate
                      J8033
                      N17.933
                      R65.2143
                      R65.2143
                      755OWENIN1MED A5MG93.133CPR - Full CodeJ96.01- Acute respiratory failure with hypoxia
                      755OWENIN1MED A5MJ18.923No CPR - Palliative and Supportive Care
                      755OWENIN1MED A5MJ44.013No CPR - Palliative and Supportive Care
                      755OWENIN1MED A5MJ44.113No CPR - Palliative and Supportive Care
                      670DONNAIN2MED A5FR57.044No CPR - IntubateA41.9- Sepsis, unspecified organism
                      755OWENIN1MED A5MI46.944No CPR - Palliative and Supportive CareJ96.01- Acute respiratory failure with hypoxia
                      755OWENIN1MED A5MN17.044
                      755OWENIN1MED A5MS22.5XXA34
                      889LEONAIN1CARD9FR57.044No CPR - Palliative and Supportive CareI21.09- ST elevation (STEMI) myocardial infarction involving other coronary artery of anterior wall
                      562RHONDAIN7NEURO1FS06.6X0A55CPR - Full CodeS06.6X0A- Traumatic subarachnoid hemorrhage without loss of consciousness, initial encounter
                      670DONNAIN2MED A5FA41.955CPR - Full CodeA41.9- Sepsis, unspecified organism
                      No CPR - Intubate
                      755OWENIN1MED A5MJ96.0155CPR - Full CodeJ96.01- Acute respiratory failure with hypoxia
                      No CPR - Palliative and Supportive Care
                      889LEONAIN1CARD9FI21.0955No CPR - Palliative and Supportive CareI21.09- ST elevation (STEMI) myocardial infarction involving other coronary artery of anterior wall
                      562RHONDAIN7NEURO1FI46.966CPR - Full CodeS06.6X0A- Traumatic subarachnoid hemorrhage without loss of consciousness, initial encounter
                      562RHONDAIN7NEURO1FS06.2X0A66CPR - Full CodeS06.6X0A- Traumatic subarachnoid hemorrhage without loss of consciousness, initial encounter
                      755OWENIN1MED A5MJ96.0266CPR - Full CodeJ96.01- Acute respiratory failure with hypoxia
                      No CPR - Palliative and Supportive Care
                        • Re: How to avoid duplicate values
                          VIJAY VIRA

                          Image.PNGThanks for posting data. It will help folks to help you.

                           

                          You could have used advanced editor to see the option for attaching file in lower right corner. Anyway need few more clarification about your data. Please review the image for questions.

                            • Re: How to avoid duplicate values
                              Ash Patel

                              Hi Vijay this is a very simple. I have 4 values for Code status(FULL, DNR, PARTIAL,NO CPR). For each ID there are 2 unique CODE STATUS value. Right now in column 2 (scan code) it has created extra row for each scan code. I have included a source file and Target file. What I want is combine the 2 CODE STATUS VALUE into one so that it does not create duplicate row. I am thinking of putting something like IF(CODE_STATUS = 'FULL','DNR','PARTIAL','NO CPR' then 'FULL', & 'DNR', & 'PARTIAL' & 'NO CPR').

                               

                                                                                                              

                                SOURCE  

                              IDScan codeCode_Status
                              6519B19.20FULL
                              6519B19.20No CPR
                              6519D64.9FULL
                              6519D64.9No CPR
                              6519D75.89FULL
                              6519D75.89No CPR
                              6519E86.9FULL
                              6519E86.9No CPR
                              6519E87.6FULL
                              6519E87.6No CPR
                              6519F10.230FULL
                              6519F10.230No CPR
                              2356H57.02DNR
                              2356H57.02PARTIAL
                              2356I25.10DNR
                              2356I25.10PARTIAL
                              2356K76.0DNR
                              2356K76.0PARTIAL
                              2356N18.9DNR
                              2356N18.9PARTIAL
                              2356R00.1DNR
                              2356R00.1PARTIAL
                              2823R74.0FULL
                              2823R74.0PARTIAL
                              2823Y84.8FULL
                              2823Y84.8PARTIAL
                              2823Z21FULL
                              2823Z21PARTIAL
                              2823Z66

                              FULL

                               

                              2823Z66PARTIAL

                               

                               

                                TARGET  

                              IDScan codeCode_Status
                              6519B19.20FULL & No CPR
                              6519D64.9FULL & No CPR
                              6519D75.89FULL & No CPR
                              6519E86.9FULL & No CPR
                              6519E87.6FULL & No CPR
                              6519F10.230FULL & No CPR
                              2356H57.02DNR & PARTIAL
                              2356I25.10DNR & PARTIAL
                              2356K76.0DNR & PARTIAL
                              2356N18.9DNR & PARTIAL
                              2356R00.1DNR & PARTIAL
                              2823R74.0FULL & PARTIAL
                              2823Y84.8FULL & PARTIAL
                              2823Z21FULL & PARTIAL
                              2823Z66FULL & PARTIAL
                                • Re: How to avoid duplicate values
                                  VIJAY VIRA

                                  Hi Ash,

                                   

                                  Sorry for delay as I was away and busy. I'm not sure if you have found a solution or not.

                                   

                                  Here is the script I've for your need.

                                   

                                  ScanData:
                                  LOAD ID,
                                  [Scan code],
                                  Code_Status
                                  FROM
                                  [Test4.xlsx]
                                  (
                                  ooxml, embedded labels, table is Sheet1);

                                  NoConcatenate
                                  ScanData2:
                                  LOAD ID,
                                  [Scan code],
                                  AutoNumberHash128(ID, [Scan code]) as Distinct_ID_Scan_Code_Combo,
                                  Count(DISTINCT Code_Status) as Count_Code_Status
                                  Resident ScanData
                                  Group By ID, [Scan code]
                                  ;

                                  LET vMaxIDScanComboCount = Peek('Distinct_ID_Scan_Code_Combo');

                                  FOR i = 0 to $(vMaxIDScanComboCount) - 1

                                  LET vID = Peek('ID', $(i), 'ScanData2');
                                  LET vScancode = Peek('Scan code', $(i), 'ScanData2');
                                  LET vDistinct_ID_Scan_Code_Combo = Peek('Distinct_ID_Scan_Code_Combo', $(i), 'ScanData2');
                                  LET vCountCodeStatus = Peek('Count_Code_Status', $(i), 'ScanData2');

                                  NoConcatenate
                                  ScanData3:
                                  LOAD ID,
                                  [Scan code],
                                  Code_Status
                                  Resident ScanData
                                  Where ID = '$(vID)' and
                                  [Scan code] = '$(vScancode)'
                                  Order By ID, [Scan code]
                                  ;

                                  LET vCodeStatus = Peek('Code_Status', 0, 'ScanData3');

                                  FOR j = 1 to $(vCountCodeStatus) - 1;

                                  LET vCodeStatus2 = Peek('Code_Status', $(j), 'ScanData3');
                                  SET vCodeStatus = $(vCodeStatus) & $(vCodeStatus2);

                                  NEXT;

                                  FinalScanData:
                                  LOAD FSD_ID,
                                  FSD_Scan_Code,
                                  FSD_Code_Status
                                  Inline [FSD_ID, FSD_Scan_Code, FSD_Code_Status
                                  $(vID), $(vScancode), $(vCodeStatus)]
                                  ;

                                  DROP Table ScanData3;
                                  LET vCodeStatus = Null();

                                  NEXT;

                                  DROP Table ScanData, ScanData2;

                                   

                                  CombinedStatus.PNG

                      • Re: How to avoid duplicate values
                        Sasidhar Parupudi

                        May be use a straight table with

                        dimensions

                        IDCodePrimary Code

                         

                         

                        expression

                        Concat(Code_status,',') as Code_status