3 Replies Latest reply: Aug 16, 2017 5:02 AM by Christian Schmitz RSS

    count fieldvalues (distinct) in datamodel

    Christian Schmitz

      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