Solved: Re: How to avoid duplicate values - Qlik Community

ashokkumar24 · ‎2018-04-26

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

ID	Code	Code_Status	Primary Code
4	P07.03	CPR-Full Code	STEMI
4	P07.03	No CPR	STEMI
4	C67.9	CPR-Full Code	STEMI
4	C67.9	No CPR	STEMI
4	C68.9	CPR-Full Code	STEMI
4	C68.9	No CPR	STEMI
4	D50.9	CPR-Full Code	STEMI
4	D50.9	No CPR	STEMI
4	E21.3	CPR-Full Code	STEMI
4	E21.3	No CPR	STEMI
5	E78.5	CPR-Full Code	VTE
5	E78.5	No CPR	VTE
5	E86.1	CPR-Full Code	VTE
5	E86.1	No CPR	VTE
5	G89.3	CPR-Full Code	VTE
5	G89.3	No CPR	VTE
5	J21.9	CPR-Full Code	VTE
5	J21.9	No CPR	VTE
5	P29.12	CPR-Full Code	VTE
5	P29.12	No CPR	VTE

vvira1316 · ‎2018-05-09

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;

View solution in original post

vvira1316 · ‎2018-04-26

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

sasiparupudi1 · ‎2018-04-26

May be use a straight table with

dimensions

ID

Code

Primary Code

expression

Concat(Code_status,',') as Code_status

ashokkumar24 · ‎2018-04-26

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 · ‎2018-04-26

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 · ‎2018-04-26

Attach are my source data

ID	Age	Name	Class	LOS	Unit	Department	Gender	Code	ROM	SOI	Code_Status	Primary_code
4	74	SMITH	IN	1	NEURO	1	F	E11.9	2	1	CPR - Full Code	S06.6X0A- Traumatic subarachnoid hemorrhage without loss of consciousness, initial encounter
											No CPR - Intubate
								K76.89	2	1
								R40.2430	1	1
								S42.302A	1	1
								W10.9XXA	1	1
								Y92.9	1	1
								Z66	1	1
								Z79.4	1	1
								D68.9	2	2
								G93.89	2	2
								S06.5X0A	5	5
								S02.119A	6	6
								S06.1X0A	6	6
								S06.6X0A	6	6
5	62	RHONDA	IN	7	NEURO	1	F	B18.2	1	1	CPR - Full Code	S06.6X0A- Traumatic subarachnoid hemorrhage without loss of consciousness, initial encounter
								E16.2	1	1
								F11.10	1	1
								F14.10	1	1
								F17.210	1	1
								F41.8	1	1
								G89.18	1	1
								G93.2	1	1
								I10	1	1
								R09.02	1	1
								R40.2410	1	1
								S42.022A	1	1
								W17.89XA	1	1
6	70	DONNA	IN	2	MED A	5	F	E78.5	1	1	CPR - Full Code	A41.9- Sepsis, unspecified organism
								F32.9	1	1	No CPR - Intubate
								G89.29	1	1
								H40.9	1	1
								I25.10	1	1
								K52.9	1	1
								K59.00	1	1
								M19.041	1	1
								M19.042	1	1
								M54.9	1	1
								R09.02	1	1
								Z79.01	1	1
								Z85.3	1	1
								Z86.718	1	1
								Z87.891	1	1
								Z88.8	1	1
								Z96.641	1	1
								Z96.653	1	1
								Z98.41	1	1
								Z98.42	1	1
7	55	OWEN	IN	1	MED A	5	M	D64.9	1	1	CPR - Full Code	J96.01- Acute respiratory failure with hypoxia
											No CPR - Palliative and Supportive Care
								E78.00	1	1
								F10.10	1	1
								F17.200	1	1
								F31.9	1	1
								F41.0	1	1
								F41.9	1	1
								F43.10	1	1
								G47.00	1	1
								I12.9	1	1
								K21.9	1	1
								N18.9	1	1
								X58.XXXA	1	1
								Y90.0	1	1
								Y92.9	1	1
								Z51.5	1	1
								Z66	1	1
8	89	LEONA	IN	1	CARD	9	F	E78.5	1	1	No CPR - Palliative and Supportive Care	I21.09- ST elevation (STEMI) myocardial infarction involving other coronary artery of anterior wall
								F03.90	2	1
								I10	1	1
								I25.10	1	1
								I45.10	2	1
								M19.90	1	1
								Z51.5	1	1
								Z66	1	1
5	62	RHONDA	IN	7	NEURO	1	F	E87.5	1	2	CPR - Full Code	S06.6X0A- Traumatic subarachnoid hemorrhage without loss of consciousness, initial encounter
							F	G81.94	1	2
							F	I72.0	2	2
							F	I95.9	2	2
							F	S22.42XA	1	2
							F	T79.7XXA	2	2
							F	T80.818A	1	2
6	70	DONNA	IN	2	MED A	5	F	D68.9	2	2	CPR - Full Code	A41.9- Sepsis, unspecified organism
											No CPR - Intubate
								E66.2	1	2	No CPR - Intubate	A41.9- Sepsis, unspecified organism
								E86.1	1	2	No CPR - Intubate	A41.9- Sepsis, unspecified organism
								G62.9	2	2	CPR - Full Code	A41.9- Sepsis, unspecified organism
								I11.0	2	2	No CPR - Intubate	A41.9- Sepsis, unspecified organism
								I25.5	1	2	No CPR - Intubate	A41.9- Sepsis, unspecified organism
								I48.0	2	2	No CPR - Intubate	A41.9- Sepsis, unspecified organism
								Z68.41	1	2	No CPR - Intubate	A41.9- Sepsis, unspecified organism
								Z99.81	2	2	No CPR - Intubate	A41.9- Sepsis, unspecified organism
7	55	OWEN	IN	1	MED A	5	M	E87.2	2	2	No CPR - Palliative and Supportive Care	J96.01- Acute respiratory failure with hypoxia
								G25.3	1	2	CPR - Full Code
8	89	LEONA	IN	1	CARD	9	F	I95.9	3	2	No CPR - Palliative and Supportive Care	I21.09- ST elevation (STEMI) myocardial infarction involving other coronary artery of anterior wall
5	62	RHONDA	IN	7	NEURO	1	F	E87.4	2	3	CPR - Full Code	S06.6X0A- Traumatic subarachnoid hemorrhage without loss of consciousness, initial encounter
5	62	RHONDA	IN	7	NEURO	1	F	G91.3	2	3
5	62	RHONDA	IN	7	NEURO	1	F	J44.9	1	3
5	62	RHONDA	IN	7	NEURO	1	F	S27.2XXA	2	3
6	70	DONNA	IN	2	MED A	5	F	I50.23	3	3	CPR - Full Code	A41.9- Sepsis, unspecified organism
											No CPR - Intubate
								J80	3	3
								N17.9	3	3
								R65.21	4	3
								R65.21	4	3
7	55	OWEN	IN	1	MED A	5	M	G93.1	3	3	CPR - Full Code	J96.01- Acute respiratory failure with hypoxia
7	55	OWEN	IN	1	MED A	5	M	J18.9	2	3	No CPR - Palliative and Supportive Care
7	55	OWEN	IN	1	MED A	5	M	J44.0	1	3	No CPR - Palliative and Supportive Care
7	55	OWEN	IN	1	MED A	5	M	J44.1	1	3	No CPR - Palliative and Supportive Care
6	70	DONNA	IN	2	MED A	5	F	R57.0	4	4	No CPR - Intubate	A41.9- Sepsis, unspecified organism
7	55	OWEN	IN	1	MED A	5	M	I46.9	4	4	No CPR - Palliative and Supportive Care	J96.01- Acute respiratory failure with hypoxia
7	55	OWEN	IN	1	MED A	5	M	N17.0	4	4
7	55	OWEN	IN	1	MED A	5	M	S22.5XXA	3	4
8	89	LEONA	IN	1	CARD	9	F	R57.0	4	4	No CPR - Palliative and Supportive Care	I21.09- ST elevation (STEMI) myocardial infarction involving other coronary artery of anterior wall
5	62	RHONDA	IN	7	NEURO	1	F	S06.6X0A	5	5	CPR - Full Code	S06.6X0A- Traumatic subarachnoid hemorrhage without loss of consciousness, initial encounter
6	70	DONNA	IN	2	MED A	5	F	A41.9	5	5	CPR - Full Code	A41.9- Sepsis, unspecified organism
											No CPR - Intubate
7	55	OWEN	IN	1	MED A	5	M	J96.01	5	5	CPR - Full Code	J96.01- Acute respiratory failure with hypoxia
											No CPR - Palliative and Supportive Care
8	89	LEONA	IN	1	CARD	9	F	I21.09	5	5	No CPR - Palliative and Supportive Care	I21.09- ST elevation (STEMI) myocardial infarction involving other coronary artery of anterior wall
5	62	RHONDA	IN	7	NEURO	1	F	I46.9	6	6	CPR - Full Code	S06.6X0A- Traumatic subarachnoid hemorrhage without loss of consciousness, initial encounter
5	62	RHONDA	IN	7	NEURO	1	F	S06.2X0A	6	6	CPR - Full Code	S06.6X0A- Traumatic subarachnoid hemorrhage without loss of consciousness, initial encounter
7	55	OWEN	IN	1	MED A	5	M	J96.02	6	6	CPR - Full Code	J96.01- Acute respiratory failure with hypoxia
											No CPR - Palliative and Supportive Care

vvira1316 · ‎2018-04-27

Thanks 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 · ‎2018-05-03

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

ID	Scan code	Code_Status
6519	B19.20	FULL
6519	B19.20	No CPR
6519	D64.9	FULL
6519	D64.9	No CPR
6519	D75.89	FULL
6519	D75.89	No CPR
6519	E86.9	FULL
6519	E86.9	No CPR
6519	E87.6	FULL
6519	E87.6	No CPR
6519	F10.230	FULL
6519	F10.230	No CPR
2356	H57.02	DNR
2356	H57.02	PARTIAL
2356	I25.10	DNR
2356	I25.10	PARTIAL
2356	K76.0	DNR
2356	K76.0	PARTIAL
2356	N18.9	DNR
2356	N18.9	PARTIAL
2356	R00.1	DNR
2356	R00.1	PARTIAL
2823	R74.0	FULL
2823	R74.0	PARTIAL
2823	Y84.8	FULL
2823	Y84.8	PARTIAL
2823	Z21	FULL
2823	Z21	PARTIAL
2823	Z66	FULL
2823	Z66	PARTIAL

TARGET

ID	Scan code	Code_Status
6519	B19.20	FULL & No CPR
6519	D64.9	FULL & No CPR
6519	D75.89	FULL & No CPR
6519	E86.9	FULL & No CPR
6519	E87.6	FULL & No CPR
6519	F10.230	FULL & No CPR
2356	H57.02	DNR & PARTIAL
2356	I25.10	DNR & PARTIAL
2356	K76.0	DNR & PARTIAL
2356	N18.9	DNR & PARTIAL
2356	R00.1	DNR & PARTIAL
2823	R74.0	FULL & PARTIAL
2823	Y84.8	FULL & PARTIAL
2823	Z21	FULL & PARTIAL
2823	Z66	FULL & PARTIAL

vvira1316 · ‎2018-05-09

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;

ashokkumar24 · ‎2018-05-10

Thank You Vijay. This was great