If Statement, is there a better way?

[mapratt82]

I'm working on transaction data to find customer paying competitors. The data isn't straight forward, so I've had to find common characteristics that I can match on to decipher who the payment is going too. This has led to a moderately long if statement with more needing added. I need a better alternative as with this being transaction data includes millions of lines of data, thus with the If Statements makes it even slower and clunkier. Thanks!

if(WildMatch(Notes, 'Regions*'), 'Regions Bank',

if(WildMatch(Notes, '*Suntrust*'), 'Suntrust Bank',

if(WildMatch(Notes, 'Redstone F*'), 'Redstone Federal Credit Union',

if(WildMatch(Notes, 'Family Security *'), 'Family Security Credit Union',

if(WildMatch(Notes, 'Community Spirit *'), 'Community Spirit Bank',

if(WildMatch(Notes, 'Valley Credit *'), 'Valley Credit Union',

if(WildMatch(Notes, 'Valley State *'), 'Valley State Bank',

if(WildMatch(Notes, 'First Metro *'), 'First Metro Bank',

if(WildMatch(Notes, 'BBVA *','BBVACompass*'), 'BBVA/Compass Bank',

if(WildMatch(Notes, 'BB and T *', 'BB&T *', 'BBT_*'), 'BB&T Bank',

if(WildMatch(Notes, 'Barclay Card*','Barclaycard*','Barclays Bank De Transfer*'), 'Barclays US',

if(WildMatch(Notes, 'Bank of the West*', 'Bankofthewest*'), 'Bank of the West',

if(WildMatch(Notes, 'Bank of Ozarks*','Bankoftheozarks*'), 'Bank of the Ozarks',

if(WildMatch(Notes, 'Lister Hill*', 'Listerhil*'), 'Listerhill Credit Union',

if(WildMatch(Notes, 'Bancorpsouth*'), 'BancorpSouth Bank',

if(WildMatch(Notes, 'Discover*'), 'Discover',

if(WildMatch(Notes, 'Ally*') and not WildMatch(Notes,'*Paypal*'),'Ally Bank',

if(WildMatch(Notes, 'Bank Independent*'), 'Bank Independent'))))))))))))))))))

[dplr-rn]

i would use

pick(wildmatch(Notes,'*Regions*','*Suntrust*'),'Regions Bank','Suntrust Bank'))

thats mainly because IMO pick and match/wildmatch combination is better readable and maintainable than a nested if() statements, that's why I prefer to use it

There is some debate on performance impact. see below

Why is using Pick and Match functions combination thought to be faster than nested IF statements?

[Vegar]

There is a quite handy SUB available in the QlikView Components by rwunderlich called Qvc.CreateWildMapExpression.

It is a pick match in the background, but the setup is similar to a mapping table.

Check out the project at https://github.com/RobWunderlich/Qlikview-Components

[rwunderlich · Accepted solution]

And there is a non-QVC version of the wildcard mapping available here:

Qlikview Cookbook: Mapping With Wildcards http://qlikviewcookbook.com/recipes/download-info/mapping-with-wildcards/

[mapratt82]

Thank all of you for your responses. Unfortunately, I currently cannot view qvw files. I've currently have a request in to get Qlik Sense Desktop install, so with any luck maybe today or tomorrow I will be able to see what you are suggesting. I did try the pick(match()), but didn't really see much improvement. I do think I will try using it in the future some though. Thanks again!

[rwunderlich]

Mark,

I've added a qvf version of the Wildcard Mapping example.  Use the same link as above but click the "S" download button.

-Rob

[vkish16161]

Mapsubstring should do the trick. But you may have to engineer it a little bit. Also, it's best to do this in the back end.

[dplr-rn]

Nice one as usual Rob.

FYI both download links are downloading qvf now

[rwunderlich]

Thanks for pointing out the download problem. Fixed.

-Rob

[JustinDallas]

I've tried to create a demo app for you, but I can't seem to be able to scramble the strings correctly. But my pseudo code looks like this.

1. Load the Standard Company Names into a table named StandardCompanyNames

2. Load the RecordId, RecordCompanyName into a table named RecordCompanyNames

3. Cross Join the StandardCompanyName and RecordCompanyNames tables into a table named NameCrossJoin

4. Load all fields from the NameCrossJoin, RecordId & '/' & LevValue as 'LevKey';  where you calculate LevValue with LevenshteinDist(RecordCompanyNames ,StandardCompanyName ).  Call this table NameCrossJoin2

5. Load RecordId , RecordId & '/' & MIN(LevValue)  AS 'LevKey' GROUP BY RecordId into a table called MinLevValue

6. Left join MinLevValue with NameCrossJoin2 on 'LevKey' into a table called NameCrossJoin3. Add the clause "1 AS 'Is Standard Company Match'".

7. Load RecordId , StandardCompanyName from NameCrossJoin3 WHERE "Is Standard Company Match = 1" into NameCrossJoin4.

NameCrossJoin4 then can be used in a  mapping load based on the RecordId, of course, this all depends on the dirtness of your initial data.