5 Replies Latest reply: Nov 18, 2015 4:39 AM by Boris Adloff RSS

    Comparing Text Strings

    Drew Collins

      Hi,

       

      As part of our data checks, I have a table like below:

       

      Material BoughtMaterial Used
      Brick; Stone; MortarBrick
      Stone; RockRock; Wall Paper
      Brick, PebblesStone

       

      I need to ensure that what is in column B is also in column A.

       

      In the above example, row 1 is fine, but rows 2 and 3 are an issue. How can I compare these two fields for "likeness" without writing a bunch of if statements?

       

      Thanks in advance!

        • Re: Comparing Text Strings
          Anand Chouhan

          Hi,

           

          But in second you have also Rock string do you find the first or second position of the string or it just presence of the strings in the field.

           

          Regards

          Anand

          • Re: Comparing Text Strings
            Clever Anjos

            Do you want a "one-to-one" comparison?

            Could you describe better your requirement, since "Brick" appears on first column, but what if have more values?

              • Re: Comparing Text Strings
                Drew Collins

                I would essentially like to write something like below:

                 

                IF([Material Used] like [Material Bought],'Okay','Not Okay')

                 

                and this would evaluate the two to see if the string from Material Used is contained within Material Bought. I want to avoid having to write if statements containing all the possible values.

              • Re: Comparing Text Strings
                Boris Adloff

                Hi Drew,

                 

                if I get you right, you have defined items, separated by a semicolon?
                or is it undefined strings you want to compare like

                Material BoughtMaterial Used
                BrickStone; MortarBricks
                StoneCobblestone

                 

                That would make a different story, guess you would have to run loops on that. Ghastly.

                 

                 

                As long as you can split up the items using a defined separator (";") as given in your sample, you may want to extract data (using the subfield command) into two tables, one for column 1 and one for the other, each with an id for the line and a field for the text.

                 

                The data would look like this:

                Original table:

                BoughtUsed
                Brick; MortarBrick
                StoneSand; Mortar

                 

                 

                Table_Bought:

                 

                Line_IDitem
                1Brick
                1Mortar
                2Stone

                 

                 

                 

                 

                 

                 

                Table_used:

                Line_IDitem
                1Brick
                2Sand
                2Mortar

                 

                Then, grouped per line_id, you count matches of both fields,
                here resulting in a sum of 1 for line_id 1 and 0 for line_id 2.

                 

                Any line with >0 matches is what you are looking for.

                 

                Presto.

                 

                Would that help?