How to remove duplicates

[#1]

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.

[Nicole-Smith]

=count(distinct Seller & [Sale day])

See attached for an example using your data.

[#3]

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.

[Anonymous]

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)

[Anonymous]

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

[#6]

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?

[Nicole-Smith]

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.

[#8]

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.

[#9]

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.

[Oleg_Troyansky · Accepted solution]

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