topic Re: Comparing 2 columns and then finding duplicates in QlikView

Comparing 2 columns and then finding duplicates

amitvermancl — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I have 2 columns A & B, ideally they should have one to one mapping that is a entry in A should occur only once in B.

But in my case, the same entry is repeating multiple times in B, so I need to find the count of how many such duplicates are happening and count of duplicate entries (as in current example 1 is coming 3 times in B, 2 is repeating 4 times)

A	B
1	1
2	1
3	1
4	2
5	2
6	2
7	2
8	9
9	9
10	9

Re: Comparing 2 columns and then finding duplicates

Saravanan_Desingh — Thu, 20 Aug 2020 14:30:19 GMT

One solution is.

tab1: LOAD * INLINE [ A, B 1, 1 2, 1 3, 1 4, 2 5, 2 6, 2 7, 2 8, 9 9, 9 10, 9 ]; Left Join(tab1) LOAD B, Count(B) As CntB Resident tab1 Group By B;

Re: Comparing 2 columns and then finding duplicates

Kushal_Chawda — Thu, 20 Aug 2020 15:10:07 GMT

tab1: LOAD * INLINE [ A, B 1, 1 2, 1 3, 1 4, 2 5, 2 6, 2 7, 2 8, 9 9, 9 10, 9 11,5 ];

With column B as dimension you can use below expression

=Count({<B={"=count(B)>1"}>}B)

Re: Comparing 2 columns and then finding duplicates

nevilledhamsiri — Thu, 20 Aug 2020 18:48:45 GMT

Something like this?

Re: Comparing 2 columns and then finding duplicates

nevilledhamsiri — Thu, 20 Aug 2020 18:50:46 GMT

Something like this?

Re: Comparing 2 columns and then finding duplicates

Brett_Bleess — Thu, 03 Sep 2020 13:58:56 GMT

@Kushal_Chawda , @Saravanan_Desingh Hey guys, I suspect the notification issue may have caused you not to see the updates on this one, so just wanted to let you know, I think it better to let you guys chime in again versus me trying to help given it is Development related, and we know that is not my strong area! 🙂

Cheers,
Brett