topic Re: Please help in distinct count in QlikView

Please help in distinct count

Mon, 19 Oct 2015 12:20:26 GMT

I have a data like this

BU, Supplier

A, a

A, b

A, c

B, a

B, d

B, e

B, f

C, d

C, a

C, f

I want to show the number of common supplier in BUs

and I want to achieve this.

Is it possible in Qlikview?

BU	A	B	C
A
B	1
C	1	2

Re: Please help in distinct count

Gysbert_Wassenaar — Wed, 21 Oct 2015 14:12:54 GMT

See attached example.

Re: Please help in distinct count

swuehl — Wed, 21 Oct 2015 23:26:18 GMT

Here is another option.

Re: Please help in distinct count

Gysbert_Wassenaar — Thu, 22 Oct 2015 05:14:58 GMT

Follows? That's a new one for me. I can't seem to find it in the help file either. Can you explain to me how it works?

Re: Please help in distinct count

jonathandienst — Thu, 22 Oct 2015 05:24:05 GMT

Its new to me too, so I looked in the reference manual, and it is indeed there (follows and precedes). They are relational operators:

precedes ASCII less than

Unlike the < operator no attempt is made to make a numeric interpretation

of the argument values before the comparison. The operation

returns true if the value to the left of the operator has a text

representation which, in ASCII comparison, comes before the text

representation of the value on the right.

follows ASCII greater than

Unlike the > operator no attempt is made to make a numeric interpretation

of the argument values before the comparison. The operation

returns true if the value to the left of the operator has a text

representation which, in ASCII comparison, comes after the text

representation of the value on the right.

Re: Please help in distinct count

Thu, 22 Oct 2015 05:41:56 GMT

Thanks a lot.

swuehl‌ and gwassenaar‌ both seems right.

Re: Please help in distinct count

Gysbert_Wassenaar — Thu, 22 Oct 2015 05:44:01 GMT

Thanks! That certainly helps me understanding how Stefan's solution works. It seems very dependent on the sorting though, so it might not work in many other cases.

Re: Please help in distinct count

swuehl — Thu, 22 Oct 2015 06:55:55 GMT

I haven't intended to confuse anybody.

I think the main difference is within the script / data model. A simple Count(Distinct Supplier) should return correct values in the chart.

The additional if() Statement with the Follows Operator is just cosmetics, to resemble the look & feel of the OP's chart, showing only lower half of values.

Maybe I just shouldn't have used it in my example (and Gysbert is right, it may not work out under all (sort) conditions),

I just want to use these operators once in a while.

Re: Please help in distinct count

Gysbert_Wassenaar — Thu, 22 Oct 2015 07:18:32 GMT

Oh, don't worry about confusing us. I learned something new today, so thank you for that!.