topic seeing duplicates across two fields in QlikView

seeing duplicates across two fields

Wed, 04 Aug 2010 10:20:35 GMT

I have three fields supporter number, last name and postcode. What I am trying to do is to put a condition on the background colour of supporter number to say if the postcode and last name combination appears twice i want the supporter number to be coloured.

I understand where to put this condition but I cannot work out what condition to use

thanks in advance

Rosie

seeing duplicates across two fields

Miguel_Angel_Baeyens — Wed, 04 Aug 2010 11:14:32 GMT

Hello Rosie,

You may use in the color expression something like

=If(Count(PostCode & LastName) = 2, RGB(200, 200, 200))

where the "&" operator concatenates PostCode value to LastName value.

Hope that helps

seeing duplicates across two fields

Wed, 04 Aug 2010 11:37:02 GMT

Thanks Miguel

But unfortunately that is not working!

Any more help would be greatly appreciated

seeing duplicates across two fields

Miguel_Angel_Baeyens — Wed, 04 Aug 2010 11:42:50 GMT

Hello Rosie,

What fields do you want to look for? Are they already in the table?

seeing duplicates across two fields

Wed, 04 Aug 2010 11:45:29 GMT

I have a straight table of Supporter Number as the dimension and [Postcode] and [Last Name] as expressions, I want to know if there are any repetaed postcode and last name combinations and if there is colour the supporter number a different colour

Thanks

Rosie

seeing duplicates across two fields

Wed, 04 Aug 2010 12:04:14 GMT

Hi,

Sort the table with [Postcode] and [Lastname] in ascending order and try the following expression:

Normal 0 false false false EN-US X-NONE X-NONE MicrosoftInternetExplorer4

=if([Postcode]=peek([Postcode],-1),if([Lastname]=peek([Lastname],-1),rgb(100,100,100)))

hope this one works.

Cheers,

Haneesh

seeing duplicates across two fields

Wed, 04 Aug 2010 12:17:08 GMT

Sorry Haneesh this is not working either

any other suggestions?

Thanks

Rosie

seeing duplicates across two fields

Wed, 04 Aug 2010 12:49:30 GMT

Hi Rosie,

Can you attach your document with some sample data. I will try to update the expression to get the desired result.

-Haneesh

seeing duplicates across two fields

Wed, 04 Aug 2010 13:03:04 GMT

this is my document using just a sample

seeing duplicates across two fields

Wed, 04 Aug 2010 13:03:44 GMT

data

seeing duplicates across two fields

Wed, 04 Aug 2010 13:19:59 GMT

Hi Rosie,

look the attached example. Is what do you need?

Andrea

seeing duplicates across two fields

Wed, 04 Aug 2010 13:27:28 GMT

when the postcode and last name combination are repeated I need for the supporter number to go a different colour

Thanks

Rosie

seeing duplicates across two fields

Wed, 04 Aug 2010 13:31:48 GMT

Hi,

like this?

Andrea

seeing duplicates across two fields

Wed, 04 Aug 2010 13:44:46 GMT

Hi Rosie,

sorry lloking more deep I see that it's not working every time.

I keep looking.

Andrea

seeing duplicates across two fields

Wed, 04 Aug 2010 13:55:28 GMT

Hi Rosie,

now seams to work on your sample data. Take a look in the script where I have created a key field.

Andrea

seeing duplicates across two fields

Wed, 04 Aug 2010 14:12:08 GMT

Thank you very much that has worked!

Rosie

seeing duplicates across two fields

Wed, 04 Aug 2010 14:21:35 GMT

have just gone through all my data to check,as have over 10000 rows but unfortunately have now discovered has not worked

Thanks

Rosie

seeing duplicates across two fields

Wed, 04 Aug 2010 14:23:27 GMT

Can you post the data?

Andrea

seeing duplicates across two fields

Wed, 04 Aug 2010 14:28:24 GMT

no sorry I cannot

Rosie

seeing duplicates across two fields

Wed, 04 Aug 2010 14:42:11 GMT

Ok, just the 3 fields like before but with the 10.000 records?

I just tried out on one table and seams to work.

Andrea