topic Re: Duplicate rows in qlik in App Development

Duplicate rows in qlik

Maanasa — Fri, 15 Nov 2024 20:57:25 GMT

Hi, Here is my sample table, how do I eliminate duplicate rows where A--> B and B--> A , first two rows in below example needs to be considered as one single row.

Load * inline [

testID, col1, col2

1, A, B

1, B, A

1, C, A

2, D, E

3, F, H

];

thanks in advance!

Re: Duplicate rows in qlik

MeehyeOh — Fri, 31 May 2024 02:21:46 GMT

Hi, @Maanasa

You mean that the first two rows in the example should be considered as one single row because "A" is the same as "B"?

Then which one of the two lines should be left?

Is there a standard you have?

Re: Duplicate rows in qlik

Aasir — Fri, 31 May 2024 03:27:23 GMT

compare col1 with col2 and create a temp table with ordering.

create a final table with temp table data.

Re: Duplicate rows in qlik

Vegar — Fri, 31 May 2024 04:46:50 GMT

If you want A|B to be considered equal to B|A then you should consider making the rows identical in the load script.

Maybe like this:

Load testID , rangeminstring(col1,col2) as col1,rangemaxstring(col1,col2) as col2

inline [

testID, col1, col2

1, A, B

1, B, A

1, C, A

2, D, E

3, F, H

];

Re: Duplicate rows in qlik

marksouzacosta — Fri, 31 May 2024 04:53:50 GMT

Quite good exercise @Maanasa

So, to eliminate the first two records you can do the following:

InputData: LOAD testID, col1, col2, Hash128(col1, col2) AS Hash12, Hash128(col2, col1) AS Hash21 ; Load * inline [ testID, col1, col2 1, A, B 1, B, A 1, C, A 2, D, E 3, F, H ]; FinalResult: LOAD testID, col1, col2 RESIDENT InputData WHERE Not Exists(Hash12,Hash21) ; DROP TABLE InputData;

This will be your end result:

Re: Duplicate rows in qlik

Lech_Miszkiewicz — Fri, 31 May 2024 05:03:37 GMT

I like your approach @marksouzacosta - well done!

Re: Duplicate rows in qlik

Rohan — Fri, 31 May 2024 07:33:12 GMT

Hi @Maanasa ,

The only addition to @Vegar 's script is, you will have to do is add Distinct while loading then you eliminate the duplicate row, Refer the attached image :

@marksouzacosta , your code is cool too, but it will completely eliminate the duplicates, where as atleast one of them is to be retrieved in the data. Also, we can simply do a mapping load too to achieve the same result :

Regards,

Rohan.

Re: Duplicate rows in qlik

Maanasa — Fri, 31 May 2024 13:44:27 GMT

Thanks Mark!, I dont want to eliminate first two rows I want to keep one of them. this is deduping exercise. I need either one of the first two row. A-> B or B--> A.

Re: Duplicate rows in qlik

Maanasa — Fri, 31 May 2024 14:21:35 GMT

Thank you so much! that actually worked. 🙂