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).
LOAD Trade_Lane as Allocation_Trade_Lane,
Allocation_Week as Allocation_From_Week,
[Customer Code] as Allocation_Customer_Code,
RecNo() as %Allocation_IntervalID
Load *, RowNo() as Allocation_Rank_From_Week,
RecNo() as Allocation_Rank_From_Week2;
Load DISTINCT Allocation_From_Week as Count_Allocation_From_Week
Does anybody have a better idea?
The aim should be a new field with numbers 1,2,3,4 etc. to select.