3 Replies Latest reply: Oct 14, 2011 5:40 PM by vct.8311 RSS

    WildMatch---Gone Wild

      This started of as a simple request to group by account codes.  At first I thought that I could just substring out the numbers that I needed to match to.  But on further inspection I found that the numbers were structured in several different ways.  So I used to code in my load statement below to match the account codes to there category:



      if(WildMatch(AccountCodeFull,'*500140*') > 0,'North',

        if(WildMatch(AccountCodeFull,'*211340*') > 0,'South',

         if(WildMatch(AccountCodeFull,'*231402*') > 0,'East',

          if(WildMatch(AccountCodeFull,'*211440*') > 0,'West',

           if(WildMatch(AccountCodeFull,'*500240*') > 0,'Left',

            if(WildMatch(AccountCodeFull,'*127427*') > 0,'Right',

             if(WildMatch(AccountCodeFull,'*124166*') > 0,'Up',

              if(WildMatch(AccountCodeFull,'*147119*') > 0,'Down',

               if(WildMatch(AccountCodeFull,'*127146*') > 0,'Sideways',

      ))))))))) as GLDescription1


      This works well with a limited load, however they full load which used to take 8 minutes has been running for an hour and a half.


      Is there a better way?



        • Re: WildMatch---Gone Wild
          Rakesh Mehta



          pick(WildMatch(AccountCodeFull,'*500140*', '*211340*', '*231402*', '*211440*', '*500240*', '*127427*', '*124166*', '*147119*', '*127146*'), 'North', 'South', 'East', 'West', 'Left', 'Right', 'Up', 'Down', 'Sideways') as GLDescription1


          EDIT: comma was missing. Added.

          • WildMatch---Gone Wild
            Chris Cammers

            I just recently posted a blog article about this sort of thing. Check it out http://biventure.blogspot.com/2011/09/wild-thing-ill-map-your-pattern-strings.html


            You can use your pattern string arguments all in one call of wildmatch like this


            WildMatch(AccountCodeFull,'*500140*', '*211340*', '*231402*', '*211440*', '*500240*', '*127427*', '*124166*', '*147119*', '*127146*')


            Having done that you'll be left with a number so you can use an inline table or whatever to generate a map.


            There are more specific details in the blog so please take a look.

            • Re: WildMatch---Gone Wild

              Thanks--Rakesh and Chris.


              It seemed to keep hanging up for some reason.  I threw some more infratructure at it and it loads in a reasonable amount of time.


              I have to do these kind a transformations often, so I'll work with the Pick and WildMatch again.