6 Replies Latest reply: May 10, 2011 9:28 PM by Chris Cammers RSS

    Function like "contain" in the script for multiple substrings

      Hi!

       

      Does any body have a example or idea for...

       

      Basically I need to find not a single substring, but search among a list with multiples substrings…

       

      So that when I would be running script for TABLE 2 I would like to find the codes listed in TABLE 1 in the field called text.

       

      The codes into de field called text does not follow any pattern.... such as between ( ), " " etc...That's why I need to look individually for all the codes into de field text.

       

      TABLE 1:

      Code

      CAD12

      DDD25

      B6D60

      CDD12

      4DD40

       

      TABLE 2:

      User      Text

      Alex      Random text /CDD12/ random text

      Rudolf    Another random text *CAD12* random text

      Paul      Other text (DDD25) text

      John      Text 4DD40 random text

       

       

      The result expected

      User      Text                                                                     Code

      Alex      Random text /CDD12/ random text                         CDD12

      Rudolf    Another random text *CAD12* random text             CAD12

      Paul      Other text (DDD25) text                                         DDD25

      John      Text 4DD40 random text                                       4DD40

       

       

      I thought about these functions (FindOneOf or  substringcount ) but I not so sure..

       

      Table:

      LOAD

           [user],

           FindOneOf([Text],[Code],0)

          substringcount([Text],[Code])

       

       

      Any ideas or examples?

       

      THANKS!

        • Function like "contain" in the script for multiple substrings

          Give this a shot....

           

          mid(Text,index(Text,'CDD'),5) as 'Code'

           

          That's only gonna work if all of the codes are CDD## which the 5 you posted are so maybe that will be fine.

            • Function like "contain" in the script for multiple substrings

              I really appreciate your help, but the Code table does not have a pattern also..

               

              Code

              CDD12

              CDD25

              CDD60

              CDD12

              CDD40

               

              So the code does not always begin with CDD nor necessarily have 5 digits.

               

              Do you have any other idea?

               

              Someone else has?

               

              Thanks

                • Function like "contain" in the script for multiple substrings

                  Well in the example you just posted again all of the codes start with CDD and have 2 numbers so... show me one that is different b/c I think one of us is misunderstanding and I'm not sure who that is yet.

                    • Function like "contain" in the script for multiple substrings

                      I'm really sorry for the example given.

                       

                      I have just edited the original topic

                      I think now it would be easier to understand what I meant wiht Code table does not have a pattern also..

                       

                      Thanks!

                        • Function like "contain" in the script for multiple substrings

                          Yeah that makes it a lot harder...

                           

                          I have to figure there is some way to do this with peek and a for loop but that could be a nightmare if you have a LOT of data.  Maybe try something like this...

                           

                          For i = 1 to  NoOfRows('Table1')

                           

                               LET vCode=peek('Table1', 1-$(i),'Code');

                           

                          LOAD

                          User,

                          Text,

                          $(vCode) as 'Code'

                          INLINE [

                          inline stuff goes here

                          ]

                          WHERE index(Text,$(vCode))>0;

                           

                          Next i

                            • Function like "contain" in the script for multiple substrings
                              Chris Cammers

                              I think your best bet take a couple of steps, what you'll want to do is load all the strings into a single variable and then use the wildmatch() function to figure out what string has the match. So I'll try to modify your example to illustrate.

                               

                              //Load the codes into your table adding a record number and asterics on the code so you can use the

                              //wildmatch function later

                              TABLE1:

                              Load * Inline [

                              Record, Code

                              1, *CAD12*

                              2, *DDD25*

                              3, *B6D60*

                              4, *CDD12*

                              5, *4DD40*

                              ];

                               

                              //Now in a temporary table you can concatenate the codes enclosed in single quotes and delimited with //commas

                               

                              CodesTemp:

                              Load

                              Concat(chr(39) & Code & chr(39), ',') as Codes

                              Resident TABLE1;

                               

                              //Store the concatenated code list into a variable

                              Let vCodeList = Peek('Codes',0,'CodesTemp');

                               

                              //The wildmatch function returns a number indicating the position of the matching list item 

                              TABLE2:

                              Load

                                  User,

                                  Text,

                                  WildMatch(Text,$(vCodeList)) as Record;

                              Load * Inline [

                                  User, Text

                                  Alex, Random text /CDD12/ random text

                                  Rudolf, Another random text *CAD12* random text

                                  Paul, Other text (DDD25) text

                                  John, Text 4DD40 random text

                                  ];

                               

                              //Finally join the table and clean up.   

                              Left Join (TABLE2)

                              Load * Resident TABLE1;

                               

                              //Clean up

                              Drop Tables TABLE1, CodesTemp;