topic Re: WildMatch---Gone Wild in QlikView

WildMatch---Gone Wild

Fri, 14 Oct 2011 19:17:32 GMT

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

Re: WildMatch---Gone Wild

disqr_rm — Fri, 14 Oct 2011 19:31:26 GMT

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.

WildMatch---Gone Wild

chriscammers — Fri, 14 Oct 2011 19:33:07 GMT

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

Fri, 14 Oct 2011 21:40:17 GMT

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.