topic count fieldvalues (distinct) in datamodel in QlikView

count fieldvalues (distinct) in datamodel

chriys1337 — Fri, 11 Aug 2017 08:21:24 GMT

Hi,

I am trying to count a field (the specific values in this field) distinct in the datamodel.

I tried in various ways, like to load the field distinct in a second table table. But it still gives a high number out, although there are just 3 distinct values inside, (not distinct it is correct).

Allocation:
LOAD Trade_Lane as Allocation_Trade_Lane,

Allocation_Week as Allocation_From_Week,
Allocation_To_Week,
[Customer Code] as Allocation_Customer_Code,
Allocation,

RecNo() as %Allocation_IntervalID
FROM
[$(QVDPath)Allocation.qvd]

(qvd);

rank:
Load *, RowNo() as Allocation_Rank_From_Week,
RecNo() as Allocation_Rank_From_Week2;

Load DISTINCT Allocation_From_Week as Count_Allocation_From_Week
Resident Allocation;

Does anybody have a better idea?

The aim should be a new field with numbers 1,2,3,4 etc. to select.

Kind Regards,

Chris

Re: count fieldvalues (distinct) in datamodel

antoniotiman — Fri, 11 Aug 2017 08:40:23 GMT

Hi Chistian,

if I have uderstood, You want a Counter for Distinct values of Field.

You can use

AutoNumber(Field) as CounterField

Regards,

Antonio

Re: count fieldvalues (distinct) in datamodel

effinty2112 — Fri, 11 Aug 2017 09:12:51 GMT

Hi Christian,

Try

=FieldValueCount('Allocation_From_Week')

this should work both in the UI and in script. Remember to enclose the field name in single quotes.

Cheers

Andrew

Re: count fieldvalues (distinct) in datamodel

chriys1337 — Wed, 16 Aug 2017 09:02:36 GMT

Thx autonumber() gave me the correct output.