topic Re: how to get distinct count in QlikView

how to get distinct count

kunkumnaveen — Thu, 27 Jul 2017 10:07:29 GMT

Below i have sample table

Material Plant Vendor Fromdate Price

001 XXX AAA 27-6-2017 100

001 XXX AAA 01-06-2017 200

001 XXX BBB 22-07-2017 20

001 YYY CCC 23-07-2017 10

001 YYY CCC 01-07-2017 30

001 ZZZ AAA 10-07-2017 105

002 ZZZ DDD 09-07-2017 103

002 ZZZ DDD 02-07-2017 113

i need show distinct vendor count and every vendors max Fromdate price counts as below .

1)Table1

Material Number of Number of different

Vendors Prices

001 3 4

002 1 1

Dimension: Material

Expression: 1)count({<Scope={'Yes'}>distinct Vendor})(For Number of Vendors)

2) How to write this expression to get Number of Different distinct latest Prices for each vendor )

2)Table2

In table2 i need to show latest Fromdate value and there respective distinct prices

Material vendor From date price

001 AAA 27-6-2017 100

001 BBB 22-07-2017 20

001 CCC 23-07-2017 10

001 AAA 10-07-2017 105

002 DDD 09-07-2017 103

Thanks

Re: how to get distinct count

kunkumnaveen — Thu, 27 Jul 2017 10:13:42 GMT

2)Table2

In table2 i need to show latest Fromdate value and there respective distinct prices

Material Plant vendor From date price

001 XXX AAA 27-6-2017 100

001 XXX BBB 22-07-2017 20

001 XXX CCC 23-07-2017 10

001 YYY AAA 10-07-2017 105

002 ZZZ DDD 09-07-2017 103

Re: how to get distinct count

tresB — Thu, 27 Jul 2017 10:17:38 GMT

May be this?

Re: how to get distinct count

kunkumnaveen — Thu, 27 Jul 2017 10:24:33 GMT

Thanks for your reply

Material Number of Number of different

Vendors Prices

001 3 4

002 1 1

if i need show count then is it possible to write count expression in firstsortvalue

Re: how to get distinct count

prma7799 — Thu, 27 Jul 2017 10:28:02 GMT

For this try like this

Re: how to get distinct count

kunkumnaveen — Thu, 27 Jul 2017 10:30:45 GMT

could u plz paste expression ....

Re: how to get distinct count

prma7799 — Thu, 27 Jul 2017 10:34:12 GMT

1.count(DISTINCT{<Fromdate = {"=max(Fromdate)"}>}Vendor)

2.count(aggr(count(Price),Material,Vendor,Plant))

Re: how to get distinct count

tresB — Thu, 27 Jul 2017 10:54:54 GMT

Dimension: Material

Exp1: Count( Distinct Vendor)

Exp2: Aggr( Count( TOTAL <Material> DISTINCT Plant&'|'&Vendor), Material, Plant)