5 Replies Latest reply on Jul 5, 2017 2:53 AM by Amit Narkar

    Count of each row .collective sum

    Ashish Vij

      Hi,

       

      Attached is the sample excel

       

      if I want count (site name) in another row. What should be my approach ?
      if I write the expression as Count(site name),it does not give me the desired result and simply display as 1 in front of every row. Please guide.Thanks

        • 1. Re: Count of each row .collective sum
          Shinichiro Murakami

          Hi Ashish

           

          Here is one example.

           

          Duplicate data source , in this case I have three.

           

           

           

           

           

           

          Thanks,

          Shin

          • 2. Re: Count of each row .collective sum
            Eugenia Kis

            Hi Ashish,

             

            First you need to reorganize /clean your data so that you have 1 site name per cell. This is what you have now:

            now.JPG

            You want to have this:

            clean.JPG

            There should be one site name per cell and it should correspond to Name and Nick name.

            Then count([Site name]) will work as you expect it.

             

            result.JPG

             

             

             

            I hope this helps.

            • 3. Re: Count of each row .collective sum
              Amit Narkar

              Hi Ashish

               

              I am assuming few things before proceeding.

              You have multiple sites and values are added in single column separated by fixed pattern.

              e.g. in you case 1.Site1  2.Site2  3.Site3 or may be by commas SIte1,Site2,Site3

               

              Number of Sites

              Len(REGEXP_REPLACE(Replace([Site name],' ',""),'[aA1-zZ9]',""))

               

              This logic removes all the blanks, small letter, capital letter and numerics from the field and displays whatever is remaning, so in above case that would be ... and LEN function calculates the length which would turn out to be same as number of sites.

               

              In order to work with this solution you have basically get rid of all other characters from the string to just have unique identifier either (. or , or anything else)

               

               

              Please find the attached workbook for your reference.

               

              Note: I personally believe the larger the data volume such string functions could get very heavy, ideally you should get your data cleaned as suggested earlier.

              • 4. Re: Count of each row .collective sum
                Ashish Vij

                Hi amit,

                 

                Thanks for the suggestion.

                In my case I am using data source as postgre sql and data is quite large. So,in that case what should be my approach to find the count ?

                 

                Thanks

                • 5. Re: Count of each row .collective sum
                  Amit Narkar

                  you can probably try to get multi valued column into separate rows. if you need to look into Site details. If you just need count of sites for current analysis without being bother about actual sites, then you can probably write a sql to calculate numbers of separators using similar logic in the back end

                   

                  I may not be able to give your further insights into postgre sql due to my lack of exposure.