WildMatch---Gone Wild

[#1]

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?

Thanks

[disqr_rm]

Try

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.

[chriscammers]

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.

[#4]

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.