Qlik Community

QlikView Extensions

Discussion Board for collaboration on QlikView Extensions.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

darrihilmarsson
Not applicable

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
sunny_talwar
Not applicable

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
colin_albert
Not applicable

Re: How to count duplicated data without using distinct

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

sunny_talwar
Not applicable

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
Not applicable

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
MarcoWedel
Not applicable

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
Not applicable

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;