20 Replies Latest reply: Mar 22, 2016 5:56 AM by Tiffany Seeland RSS

    Count Occurrences inside a Field?

    debijo _

      Is there an easy way to count how many instances of a particular character occur in a field?

       

      For instance, a field like: 

      172.1.2.3,jdoe,dell,windows xp,2734 mhz,1024 meg

      counting the commas (",") should return 5

       

      Thanks in advance,

      Debi

      8.01 Pro

        • Count Occurrences inside a Field?
          debijo _

          Is there an easy way to count how many instances of a particular character occur in a field?

           

          For instance, a field like: 

          172.1.2.3,jdoe,dell,windows xp,2734 mhz,1024 meg

          counting the commas (",") should return 5

           

          Thanks in advance,

          Debi

          8.01 Pro

          • Count Occurrences inside a Field?
            Data Kruncher

            Debi,

             

            To accomplish this, let's define a new calculated numeric field named CommaCount with this formula:

            [font="courier"]Len(MyString)-Len(Strip(MyString,","))[/font][/quote]This determines how many characters are in the original field if you take out the commas, then deducts that value from the number of characters in the original string. The result is the number of commas in the string.

             

            Kruncher

            • Count Occurrences inside a Field?
              Data Kruncher

              Debi,

               

              To accomplish this, let's define a new calculated numeric field named CommaCount with this formula:

              [font="courier"]Len(MyString)-Len(Strip(MyString,","))[/font][/quote]This determines how many characters are in the original field if you take out the commas, then deducts that value from the number of characters in the original string. The result is the number of commas in the string.

               

              Kruncher

              • Count Occurrences inside a Field?
                Grant Perkins

                That's nice!

                 

                And if the variable to be stripped was taken from a calculated field set up as a Runtime Parameter the users could have a flexible way to specify what they wanted to count.

                 

                Which might be quite useful with LSPLIT and RSPLIT if one needed to calculate in advance how many sections each row might split to based on a specific character. (For example ...)

                 

                Grant

                • Count Occurrences inside a Field?
                  Grant Perkins

                  That's nice!

                   

                  And if the variable to be stripped was taken from a calculated field set up as a Runtime Parameter the users could have a flexible way to specify what they wanted to count.

                   

                  Which might be quite useful with LSPLIT and RSPLIT if one needed to calculate in advance how many sections each row might split to based on a specific character. (For example ...)

                   

                  Grant

                  • Count Occurrences inside a Field?
                    Nick Osdale-Popa

                    Excellent, Kruncher! Tucking that little gem away for future reference.

                    • Count Occurrences inside a Field?
                      Nick Osdale-Popa

                      Excellent, Kruncher! Tucking that little gem away for future reference.

                      • Count Occurrences inside a Field?
                        Data Kruncher

                        Thanks boys!

                         

                        Keep in mind you could count occurrences of strings or numbers, and not just single characters.

                         

                        Things like this just highlight the well-designed flexibility of Monarch. Monarch is a great example of "Where there's a will, there's a way"!

                         

                        Kruncher

                        • Count Occurrences inside a Field?
                          Data Kruncher

                          Thanks boys!

                           

                          Keep in mind you could count occurrences of strings or numbers, and not just single characters.

                           

                          Things like this just highlight the well-designed flexibility of Monarch. Monarch is a great example of "Where there's a will, there's a way"!

                           

                          Kruncher

                          • Count Occurrences inside a Field?
                            debijo _

                            Ingenious.

                             

                            Thanks!

                            Debi

                            • Count Occurrences inside a Field?
                              debijo _

                              Ingenious.

                               

                              Thanks!

                              Debi

                              • Count Occurrences inside a Field?
                                Nick Osdale-Popa

                                This Character Count[/b] formula came in handy today.

                                 

                                I needed to know the field names from an SQL string:

                                [font="courier"]SELECT  

                                ( Jobs.ProjectManager ) As ProjectManager, 

                                ( Jobs.JobNumber ) As JobNumber, 

                                ( po_header.ohd_phase ) As Phase, 

                                ( po_header.ohd_cc ) As CostCode, 

                                ( ap_vendor.avm_name ) As VendorName, 

                                ( po_header.ohd_date ) As PODate, 

                                ( po_header.ohd_po ) As PONumber, 

                                ( po_header.ohd_conum ) As ChangeOrder, 

                                ( po_header.ohd_complete ) As Complete, 

                                ( po_header.ohd_type ) As POType, 

                                ( po_line.oli_line ) As LineNumber, 

                                ( po_line.oli_job ) As LineJob, 

                                ( po_line.oli_desc1 + po_line.oli_desc2 ) As Description, 

                                ( CDBL(po_line.oli_unitcost) ) As UnitCost, 

                                ( CDBL(po_line.oli_cextend) ) As ExtendedPrice, 

                                ( CDBL(po_line.oli_amtinv) ) As AmountInvoiced, 

                                ( CDBL(po_line.oli_cquant) ) As Quantity, 

                                ( po_line.oli_updinv ) As UpdateInventory, 

                                ( po_line.oli_ch_cal ) As CancelFlag, 

                                ( CDBL(po_line.oli_quantinv) ) As QuantityInvoiced, 

                                ( po_header.ohd_cat ) As Category, 

                                ( jobs.JobName ) As JobName[/font][/quote](the trapped field is called SQL[/b])

                                 

                                After defining the trap to capture the whole line (the trapped field is called SQL[/b])

                                I created two formulas

                                First the character count formula:

                                Calculated Numeric Field (Name: chrCount[/b])

                                [font="courier"]Len(SQL)-Len(Strip(SQL,"("))[/font][/quote]Then the formula to extract the fields out of the parenthesis:

                                Calculated String Field (Name: Field[/b])

                                [font="courier"]Trim(Lsplit(LSplit(SQL,chrCount1,"(",chrCount1),2,")",1))[/font][/quote]To verify that this would work with any number of parenthesises, I edited my report as such:

                                [font="courier"]SELECT  

                                ( Jobs.ProjectManager ) As ProjectManager, 

                                ( Jobs.JobNumber ) As JobNumber, 

                                ( po_header.ohd_phase ) As Phase, 

                                ( po_header.ohd_cc ) As CostCode, 

                                ( ap_vendor.avm_name ) As VendorName, 

                                ( po_header.ohd_date ) As PODate, 

                                ( po_header.ohd_po ) As PONumber, 

                                ( po_header.ohd_conum ) As ChangeOrder, 

                                ( po_header.ohd_complete ) As Complete, 

                                ( po_header.ohd_type ) As POType, 

                                ( po_line.oli_line ) As LineNumber, 

                                ( po_line.oli_job ) As LineJob, 

                                ( po_line.oli_desc1 + po_line.oli_desc2 ) As Description, 

                                ( CDBL((po_line.oli_unitcost)) ) As UnitCost, 

                                ( CDBL(((po_line.oli_cextend))) ) As ExtendedPrice, 

                                ( CDBL((((po_line.oli_amtinv)))) ) As AmountInvoiced, 

                                ( CDBL(((((po_line.oli_cquant)))) ) As Quantity, 

                                ( po_line.oli_updinv ) As UpdateInventory, 

                                ( po_line.oli_ch_cal ) As CancelFlag, 

                                ( CDBL(po_line.oli_quantinv) ) As QuantityInvoiced, 

                                ( po_header.ohd_cat ) As Category, 

                                ( jobs.JobName ) As JobName[/font][/quote]This is what my table finally looked like:

                                [font="courier"]FIELD

                                -


                                Jobs.ProjectManager

                                Jobs.JobNumber

                                po_header.ohd_phase

                                po_header.ohd_cc

                                ap_vendor.avm_name

                                po_header.ohd_date

                                po_header.ohd_po

                                po_header.ohd_conum

                                po_header.ohd_complete

                                po_header.ohd_type

                                po_line.oli_line

                                po_line.oli_job

                                po_line.oli_desc1 + po_line.oli_desc

                                po_line.oli_unitcost

                                po_line.oli_cextend

                                po_line.oli_amtinv

                                po_line.oli_cquant

                                po_line.oli_updinv

                                po_line.oli_ch_cal

                                po_line.oli_quantinv

                                po_header.ohd_cat

                                jobs.JobName[/font][/quote]So, I thank you once again, Data Kruncher, for that excellent formula!

                                • Count Occurrences inside a Field?
                                  Nick Osdale-Popa

                                  This Character Count[/b] formula came in handy today.

                                   

                                  I needed to know the field names from an SQL string:

                                  [font="courier"]SELECT  

                                  ( Jobs.ProjectManager ) As ProjectManager, 

                                  ( Jobs.JobNumber ) As JobNumber, 

                                  ( po_header.ohd_phase ) As Phase, 

                                  ( po_header.ohd_cc ) As CostCode, 

                                  ( ap_vendor.avm_name ) As VendorName, 

                                  ( po_header.ohd_date ) As PODate, 

                                  ( po_header.ohd_po ) As PONumber, 

                                  ( po_header.ohd_conum ) As ChangeOrder, 

                                  ( po_header.ohd_complete ) As Complete, 

                                  ( po_header.ohd_type ) As POType, 

                                  ( po_line.oli_line ) As LineNumber, 

                                  ( po_line.oli_job ) As LineJob, 

                                  ( po_line.oli_desc1 + po_line.oli_desc2 ) As Description, 

                                  ( CDBL(po_line.oli_unitcost) ) As UnitCost, 

                                  ( CDBL(po_line.oli_cextend) ) As ExtendedPrice, 

                                  ( CDBL(po_line.oli_amtinv) ) As AmountInvoiced, 

                                  ( CDBL(po_line.oli_cquant) ) As Quantity, 

                                  ( po_line.oli_updinv ) As UpdateInventory, 

                                  ( po_line.oli_ch_cal ) As CancelFlag, 

                                  ( CDBL(po_line.oli_quantinv) ) As QuantityInvoiced, 

                                  ( po_header.ohd_cat ) As Category, 

                                  ( jobs.JobName ) As JobName[/font][/quote](the trapped field is called SQL[/b])

                                   

                                  After defining the trap to capture the whole line (the trapped field is called SQL[/b])

                                  I created two formulas

                                  First the character count formula:

                                  Calculated Numeric Field (Name: chrCount[/b])

                                  [font="courier"]Len(SQL)-Len(Strip(SQL,"("))[/font][/quote]Then the formula to extract the fields out of the parenthesis:

                                  Calculated String Field (Name: Field[/b])

                                  [font="courier"]Trim(Lsplit(LSplit(SQL,chrCount1,"(",chrCount1),2,")",1))[/font][/quote]To verify that this would work with any number of parenthesises, I edited my report as such:

                                  [font="courier"]SELECT  

                                  ( Jobs.ProjectManager ) As ProjectManager, 

                                  ( Jobs.JobNumber ) As JobNumber, 

                                  ( po_header.ohd_phase ) As Phase, 

                                  ( po_header.ohd_cc ) As CostCode, 

                                  ( ap_vendor.avm_name ) As VendorName, 

                                  ( po_header.ohd_date ) As PODate, 

                                  ( po_header.ohd_po ) As PONumber, 

                                  ( po_header.ohd_conum ) As ChangeOrder, 

                                  ( po_header.ohd_complete ) As Complete, 

                                  ( po_header.ohd_type ) As POType, 

                                  ( po_line.oli_line ) As LineNumber, 

                                  ( po_line.oli_job ) As LineJob, 

                                  ( po_line.oli_desc1 + po_line.oli_desc2 ) As Description, 

                                  ( CDBL((po_line.oli_unitcost)) ) As UnitCost, 

                                  ( CDBL(((po_line.oli_cextend))) ) As ExtendedPrice, 

                                  ( CDBL((((po_line.oli_amtinv)))) ) As AmountInvoiced, 

                                  ( CDBL(((((po_line.oli_cquant)))) ) As Quantity, 

                                  ( po_line.oli_updinv ) As UpdateInventory, 

                                  ( po_line.oli_ch_cal ) As CancelFlag, 

                                  ( CDBL(po_line.oli_quantinv) ) As QuantityInvoiced, 

                                  ( po_header.ohd_cat ) As Category, 

                                  ( jobs.JobName ) As JobName[/font][/quote]This is what my table finally looked like:

                                  [font="courier"]FIELD

                                  -


                                  Jobs.ProjectManager

                                  Jobs.JobNumber

                                  po_header.ohd_phase

                                  po_header.ohd_cc

                                  ap_vendor.avm_name

                                  po_header.ohd_date

                                  po_header.ohd_po

                                  po_header.ohd_conum

                                  po_header.ohd_complete

                                  po_header.ohd_type

                                  po_line.oli_line

                                  po_line.oli_job

                                  po_line.oli_desc1 + po_line.oli_desc

                                  po_line.oli_unitcost

                                  po_line.oli_cextend

                                  po_line.oli_amtinv

                                  po_line.oli_cquant

                                  po_line.oli_updinv

                                  po_line.oli_ch_cal

                                  po_line.oli_quantinv

                                  po_header.ohd_cat

                                  jobs.JobName[/font][/quote]So, I thank you once again, Data Kruncher, for that excellent formula!

                                    • Re: Count Occurrences inside a Field?
                                      Tiffany Seeland

                                      Can anyone tell me why I can see the containers in this post?

                                       

                                      I hope that is the correct term for them.  They make it almost impossible to try and read what the actual post says.  I'm having no luck counting how many instances of a phrase are in my field(s).

                                       

                                       

                                      Thanks!

                                        • Re: Count Occurrences inside a Field?
                                          Olly Bond

                                          Hello Tiffany,

                                           

                                          The font & quot; bits are remnants from the migration from the old vBulletin MonarchForums to the new Jive community - let's just be grateful that the old threads and their ideas made it through...

                                           

                                          Len(MyString)-Len(Strip(MyString;".")) will return the number of occurrences of a full stop "." in a string.

                                           

                                          Best wishes,

                                           

                                          Olly

                                            • Re: Count Occurrences inside a Field?
                                              Tiffany Seeland

                                              Hi Olly, that's great, at least it isn't my system.  I read further down that the formula I did manage to suss out would work for strings, but the strip is using every character instead of my string.  What am I doing wrong?

                                               

                                               

                                              Thanks for the quick reply!

                                                • Re: Count Occurrences inside a Field?
                                                  Olly Bond

                                                  Hi Tiffany

                                                   

                                                  Strip("A man, a plan, a canal, Panama!";"a") will give you " mn, plan,  cnl, Pnm!"

                                                   

                                                  Strip("A man, a plan, a canal, Panama!";" man") will give you " ,  pl,  cl P!"

                                                   

                                                  Strip removes all the occurrences of each of the characters you tell it to remove.

                                                   

                                                  So the trick to count how many times "a" occurs in the phrase works fine, but it won't work for "man".

                                                   

                                                  So you have a big text field which might contain "Debit" (or something else) none, one, or many times, and you need to count how many times "Debit" appears? Is there any practical upper limit - like you'll never find it more than 5, 10, 20 times in the same field?

                                                   

                                                  Best wishes,

                                                   

                                                  Olly

                                                    • Re: Count Occurrences inside a Field?
                                                      Tiffany Seeland

                                                      I'm looking for "Tender Added:" to see how many times we tendered a sale.  Best case scenario is once, of course, but I'll pray no more than maybe 5?

                                                       

                                                      There are multiple scenarios; here are the most frequent:

                                                       

                                                      Regular one tender sale:

                                                       

                                                       

                                                      Cash Sale; change reported as additional tender:

                                                       

                                                      Customer adds item to sale:

                                                       

                                                       

                                                      Multiple tenders:

                                                       

                                                       

                                                      Thank you again,

                                                        • Re: Count Occurrences inside a Field?
                                                          Olly Bond

                                                          Hello Tiffany

                                                           

                                                          With small numbers like this, you can hack it with a nested calculated field.

                                                           

                                                          If(len(lsplit(String;6;"Tender Added:";6))>0;"There are at least five Tender Added";

                                                          If(len(lsplit(String;6;"Tender Added:";5))>0;"There are at least four Tender Added";

                                                          If(len(lsplit(String;6;"Tender Added:";4))>0;"There are at least three Tender Added";

                                                          If(len(lsplit(String;6;"Tender Added:";3))>0;"There are at least two Tender Added";

                                                          If(len(lsplit(String;6;"Tender Added:";2))>0;"There is at least one Tender Added";

                                                          If(len(lsplit(String;6;"Tender Added:";1))>0;"There isn't a single Tender Added"))))))

                                                           

                                                          Best wishes,

                                                           

                                                          Olly