count fieldvalues (distinct) in datamodel

[chriys1337]

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

[antoniotiman · Accepted solution]

Hi Chistian,

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

You can use

AutoNumber(Field) as CounterField

Regards,

Antonio

[effinty2112]

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

[chriys1337]

Thx autonumber() gave me the correct output.