topic Re: How to remove duplicates in QlikView

How to remove duplicates

Mon, 20 Jan 2014 19:31:59 GMT

Hi team,

I hope you are doing great. I’m kind of new working on Qlikview, and I have an issue with one formula. I hope someone can help me out.

What I’m trying to do is an expression that can remove duplicates. I have as the dimension the Supervisor ID, and each Supervisors have their own sellers. Each seller need to have an interaction day before the sale is done. They can just make one sale each day. So on my tables I have something like this, since sometimes may database duplicates the sale days.

SupervisorID	Seller	Interaction day	Sale day
1	Kenia	12/31/2013	01/03/2014
1	Kenia	12/31/2013	01/03/2014
1	Andres	12/31/2013	01/03/2014
1	Andres	12/31/2013	01/03/2014
1	Iñigo	12/2/2013	12/03/2013
1	Iñigo	12/2/2013	12/03/2013

My intention is just to count one Sale day for each interaction day, corresponding to each seller.

Meaning to have something like this:

SupervisorID	Seller	Interaction day	Sale day
1	Kenia	12/31/2013	01/03/2014
1	Serrano	12/31/2013	01/03/2014
1	Iñigo	12/2/2013	12/03/2013

the total number of Sale day is “3”

I try to use a formula like: =count(DISTINCT if(saleday=interactionday,1,0))

What happned was that using this way DISTINCT it deleated one of the 01/03/2014, so I just stayed with “2”, but Im looking to have a 3 since are diferent sellers.

NOTE: I can’t modified my script nor my database. I have to use an expression.

I hope some one can halp me with this.

Re: How to remove duplicates

Nicole-Smith — Mon, 20 Jan 2014 19:53:39 GMT

=count(distinct Seller & [Sale day])

See attached for an example using your data.

Re: How to remove duplicates

Mon, 20 Jan 2014 20:08:36 GMT

Hello,

If you don't need (or don't use) duplicates rows, try Load DISTINCT in script editor.

Example:

MyTable:

LOAD DISTINCT

Field1,

Field2,

FieldN

From MyTable.qvd;

Finally use your Count() Formula.

Best regards.

Re: How to remove duplicates

Anonymous — Mon, 20 Jan 2014 20:09:14 GMT

I believe since there can be multiple interaction days that have the same Sale Day it should be:

=count(distinct [Sale day]&[Interaction Day] &Seller)

Re: How to remove duplicates

Anonymous — Mon, 20 Jan 2014 20:28:30 GMT

Please try:

=count({<seller={'*'}>} distinct saleday) - for distinct records based on two field seller and saleday

=count({<seller={'*'}>} distinct [saleday] &[interactionday]) for distinct records based on three fields seller, interactionday and saleday

Re: How to remove duplicates

Mon, 20 Jan 2014 20:43:46 GMT

Hi Nicole,

Thanks for your prompt response, I tried to use your formula, but I just got the count distinct from all sellers and sales day, it seems that is adding total Sellers + Sale day.

Where can I see the attachment?

Re: How to remove duplicates

Nicole-Smith — Mon, 20 Jan 2014 20:46:24 GMT

You can find the attachment from looking at my post on the thread, not from in your inbox:

I actually think you may want to go with Michael Gardner's response.

Re: How to remove duplicates

Mon, 20 Jan 2014 20:46:27 GMT

Hi Oswaldo,

I considered to make changes on the script, but I’m not allowed to change it.

I’m looking forward to make it happened with a formula.

Any extra ideas?

Thanks for your response.

Re: How to remove duplicates

Mon, 20 Jan 2014 20:48:44 GMT

Hi Michael

The same thing happened as Nicole case, but in this time I got a bigger number since it seems that is adding the interaction day.

Thanks for your response.

Re: How to remove duplicates

Oleg_Troyansky — Mon, 20 Jan 2014 20:54:16 GMT

If you need to count only one Sales date per Interaction Date, you need to use an advanced aggregation function like this:

count( AGGR( min([Sale day]), SupervisorID, Seller, [Interaction Day]))

The function AGGR will pre-aggregate the detailed data and give you one Sales date for each combination of Supervisor, Seller and Interaction Date, and the function Count will count the results. If this is used as an expression in a chart with Supervisor and Seller as Dimensions, the result will accurately show the count of Sale Dates per Supervisor and Seller.

Make sure NOT to use DISTINCT because some Sellers may have the same Sale Dates, and you want to count them separately.

I teach Advanced Aggregation at a great level of detail at the Masters Summit for QlikView, which is coming to Chicago this April - check out MastersSummit.com.

Best,

Oleg Troyansky

Re: How to remove duplicates

Mon, 20 Jan 2014 21:12:34 GMT

Hi Nitha,

I tried that way and it gave me only the Dinstinct form saleday, meaning that if other Seller sold on the same day the value was eliminated since was repeated even though was form different seller.

Re: How to remove duplicates

Nicole-Smith — Mon, 20 Jan 2014 21:19:01 GMT

Please post an example dashboard so that we can help you. Right now, we're just going at it blindly, and obviously failing.

Preparing examples for Upload - Reduction and Data Scrambling

Re: Re: How to remove duplicates

Mon, 20 Jan 2014 23:16:11 GMT

True Nicole,

Please find attached one example. In this case I have the same sellers and the same duplicates dates that I posted at the beginning.

I should have 103 salesday instead of 106 , since 3 of them are repeated. The same for interaction day, its repeated by 3 since the database repeated the saleday and the corresponding data for each Seller.

Re: How to remove duplicates

Tue, 21 Jan 2014 00:14:49 GMT

"If you need to count only one Sales date per Interaction Date"

This is exactly what I need. I tried to use your formula but It didn't work. I just attached an example on my post. can you help me out with this?

Thanks.

Re: Re: Re: How to remove duplicates

Nicole-Smith — Tue, 21 Jan 2014 13:39:24 GMT

Oleg Troyansky's formula works for me. I did make one minor change to it though. Since you're displaying it on a chart where the Dimension is month, I also added Month to the aggr() function:

=count( AGGR( min(SaleDay), SupervisorID, Seller, InteractionDay, Month))

I have also attached the working .qvw file.

Re: How to remove duplicates

Fri, 30 Oct 2015 09:37:16 GMT

Dear Oleg Troyansky,

I came across your answer to a similar problem I am facing and in need of your advice/assistance to resolve this.

I need to discount the duplicate vendors (highlighted in yellow) where the DocUploadDate field is blank. I only want to display the unique vendor count using the AGGR function that you taught Braulio Nunez.

The system should count the total number of unique vendors (InviteesStatName) that have a DocUploadDate with Invitees_Status other than "Invited" AND those single record vendors without a DocUploadDate where the Invitees_Status is "Invited" (please see the second screenshot). These vendors have not had their "Invited" status updated to another status and still have a [Blank] DocUploadDate.

I used Count(AGGR(min([DocUploadDate]), InviteesStatName, [Invitees_Status])) but it only displays vendors with a DocUploadDate (in this case is minimum). It does not display vendors without a DocUploadDate that are non-duplicates with status "Invited".

Appreciate your prompt assistance on this.

Re: How to remove duplicates

Oleg_Troyansky — Mon, 02 Nov 2015 23:59:24 GMT

Hi Samuel,

your problem may sound similar, but is totally different from the other one that I responded to... In your example, I doubt if you need to use AGGR. Instead, you need to use a simple distinct count of InviteesStatName, with a Set Analysis condition that reflects the logic that you have described - (Date = Blank and Status = Invited) or (Date <> Blank and Status <> Invited). Something along these lines:

count (

{< InviteesStatName=P({<[DocUploadDate]={'[BLANK]'}, [Invitees_Status]={'Invited'}>} InviteesStatName)>} + P({<[DocUploadDate]-={'[BLANK]'}, [Invitees_Status]-={'Invited'}>} InviteesStatName)

distinct InviteesStatName)

cheers,

Oleg Troyansky

QlikView Your Business: An expert guide to Business Discovery with QlikView and Qlik Sense

Re: How to remove duplicates

Wed, 04 Nov 2015 08:14:30 GMT

Hi Oleg,

Thanks for your kind help. However, I input the script as you recommended but it is filled with errors (see below). Can you please advise what has gone wrong and what needs to be done to correct it? What exactly is the P function for?

Re: How to remove duplicates

Oleg_Troyansky — Thu, 05 Nov 2015 16:01:10 GMT

Hi Samuel,

Sorry for the sloppy syntax. When I type suggested expressions here, I get no syntax checking ... The following might work better.

count (

{< InviteesStatName=P({<[DocUploadDate]={'[BLANK]'}, [Invitees_Status]={'Invited'}>} InviteesStatName) + P({<[DocUploadDate]-={'[BLANK]'}, [Invitees_Status]-={'Invited'}>} InviteesStatName) >}

distinct InviteesStatName)

You still might have to tweak the syntax a bit...

Function P() returns possible values. I recommend reading the Help article Set Analysis, it describes the basics, or learn Set Analysis from my book QlikView Your Business.

cheers,

Oleg Troyansky

Re: How to remove duplicates

Fri, 06 Nov 2015 04:12:30 GMT

Dear Oleg,

Thanks again for trying to assist me with the syntax. Although the script editor says the Expression is OK, however, I still see red lines being drawn below at the 2nd half of the syntax. Any idea to fix that?

When I applied the change, the Bar Chart figure (circled in red) shows differently from those in the pivot table. In fact, it is going farther away from the original difference where the syntax is Count(DISTINCT {<Division=>} InviteesStatName). This is where the duplicate vendors (with a blank date and status invited but it has already changed to another status) are included in the calculation.

Look forward to hearing from you soon.

Regards,

Samuel