Qlik Community

QlikView Extensions

Discussion Board for collaboration on QlikView Extensions.

Highlighted
darrihilmarsson
New Contributor III

How to count duplicated data without using distinct

Hi, Qlikview community

I have one problem that I need help to solve.

I want to count the amount of differenct Document Numbers. It should be pretty easy but that is a twist.

The Document Number can have the same for difference Company Code.

So I have one document number 52001186 for company code 1100 and the same document number for company code 1000.

Which means that I can't use DISTINCT.

When I use DISTINCT I get total of one document number.

I want the answer to be 2 because this is two company codes. 

Any ideas?

   

Vendor NameCompany Code          Document             Number
-110052001186
-110052001186
-110052001186
-110052001186
IKEA110052001186
-100052001186
-100052001186
-100052001186
Nestle100052001186

regards Darri

Tags (1)
1 Solution

Accepted Solutions

Re: How to count duplicated data without using distinct

In terms of expression, this is what Colin Albert‌ means (if I am not wrong):

=Count(DISTINCT [Document Number] & [Company Code])

Checkout the text box object in the attached qvw.

Best,

Sunny

5 Replies

Re: How to count duplicated data without using distinct

Concatenate the Company Code & Document Number, and then calculate the distinct count.

Re: How to count duplicated data without using distinct

In terms of expression, this is what Colin Albert‌ means (if I am not wrong):

=Count(DISTINCT [Document Number] & [Company Code])

Checkout the text box object in the attached qvw.

Best,

Sunny

varunreddy
Contributor III

Re: How to count duplicated data without using distinct

Hi Darri,

Try this. This might help:

aggr(count(DISTINCT [Document Number]),[Company Code])

Output:

Company CodeDNaggr(count(DISTINCT DN),[Company Code])
1
1000520011861
1100520011861

Re: How to count duplicated data without using distinct

‌in case the numbers are not fixed length you could add a seperator to avoid collisions:

=Count(DISTINCT [Document Number] &'/'& [Company Code])


Hope this helps also.


regards


Marco

lylererger
Contributor II

Re: How to count duplicated data without using distinct

The code below - removing dublicates.

[tmpObjects]:

LOAD

  *

Where Flag=0;

LOAD

  *,

  if(Previous(ICS_Field)=ICS_Field,1,0) as Flag;

LOAD

  *

Resident Objects;

DROP Table Objects;

DROP Field Flag From tmpObjects;