QlikView Extensions

Discussion Board for collaboration on QlikView Extensions.

Announcements

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 Name Company Code Document             Number - 1100 52001186 - 1100 52001186 - 1100 52001186 - 1100 52001186 IKEA 1100 52001186 - 1000 52001186 - 1000 52001186 - 1000 52001186 Nestle 1000 52001186

regards Darri

Tags (1)
1 Solution

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

Re: How to count duplicated data without using distinct

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

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

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 Code DN aggr(count(DISTINCT DN),[Company Code]) 1 1000 52001186 1 1100 52001186 1
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

Not applicable

Re: How to count duplicated data without using distinct

The code below - removing dublicates.

[tmpObjects]:

*

Where Flag=0;

*,

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