4 Replies Latest reply: Jul 24, 2018 9:07 AM by Del Booth RSS

    Disitnct command

    Del Booth

      Hi.

       

      I am trying to determine the number of distinct routes done by vehicles in a week.  I gave a table visualisation with vehicler registration as a dimension and a measure column I have used the following command

      ColYear = year

      Colweek is week number

       

      count( {< ColYear={'2018'},ColWeek = {'28'} >} distinct Route)

       

      Where

      ColYear = year

      Colweek is week number

       

      The command works for about 90% of the vehicles but a couple don't.  I have checked vehicle registration field for issues but they are ok.  My question is does the distinct function only evaluate a section of the text.  The route field values causing the problem are:

      Route

      18-28F32SC

      18-28T32NOSC

      18-28R32SC

      18-28M32SC

       

      The command is 1 district route not 4

       

      Thanks

      Del

        • Re: Disitnct command
          Petter Skjolden

          Try this expression in the same place you use the Count function to see if you get a comma-separated list of routes or only one route:

           

               Concat( DISTINCT {< ColYear={'2018'},ColWeek = {'28'} >} Route , ',' )

          • Re: Disitnct command
            Pablo Labbe

            The distinct clause evaluates all the text in the route field, not only a part of it.

             

            18-28F32SC

            18-28T32NOSC

            18-28R32SC

            18-28M32SC

             

            if for this year and week you have records with all these 4 routes with different names, your result will be 4.

             

            More about usage of distinct clause you can find here Count or Count distinct?

            • Re: Disitnct command
              Sunny Talwar

              May be you need this

               

              Count({<ColYear={'2018'}, ColWeek = {'28'}>} DISTINCT Left(Route, 5))

               

              By doing this, you will be counting based on the first 5 characters from your string....

              • Re: Disitnct command
                Del Booth

                Thanks for the suggestions.

                 

                I did try :

                 

                Concat( DISTINCT {< ColYear={'2018'},ColWeek = {'28'} >} Route , ',' )

                 

                and got the following routes for one vehicle as an example

                 

                18-29M1
                18-29R1NO
                18-29T1
                18-29W1

                It should be

                 

                18-29M1
                18-29R1NO
                18-29T1
                18-29W1
                18-29f1

                 

                It missed off the 18-29f1, is there an issue with lower case mixes ?