Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ashokkumar24
Contributor
Contributor

How to avoid duplicate values

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
1 Solution

Accepted Solutions
vvira1316
Specialist II
Specialist II

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

View solution in original post

10 Replies
vvira1316
Specialist II
Specialist II

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

sasiparupudi1
Master III
Master III

May be use a straight table with

dimensions

IDCodePrimary Code

expression

Concat(Code_status,',') as Code_status

ashokkumar24
Contributor
Contributor
Author

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       

vvira1316
Specialist II
Specialist II

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.

ashokkumar24
Contributor
Contributor
Author

  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
vvira1316
Specialist II
Specialist II

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.

ashokkumar24
Contributor
Contributor
Author

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
vvira1316
Specialist II
Specialist II

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

ashokkumar24
Contributor
Contributor
Author

Thank You Vijay. This was great