How to count duplicated data without using distinct

[Anonymous]

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

[Colin-Albert]

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

[sunny_talwar · Accepted solution]

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]

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

[MarcoWedel]

‌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]

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;