Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
mapratt82
Creator
Creator

If Statement, is there a better way?

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'))))))))))))))))))

1 Solution

Accepted Solutions
rwunderlich

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/

View solution in original post

10 Replies
dplr-rn
Partner - Master III
Partner - Master III

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
MVP
MVP

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

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
Creator
Creator
Author

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
Creator III
Creator III

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
Partner - Master III
Partner - Master III

Nice one as usual Rob.

FYI both download links are downloading qvf now

rwunderlich

Thanks for pointing out the download problem. Fixed.

-Rob

JustinDallas
Specialist III
Specialist III

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.