Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Please visit COVID-19 group to keep up the discussion. GO TO GROUP
Contributor

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

Tags (2)
1 Solution

Accepted Solutions
Highlighted
MVP & Luminary
MVP & Luminary

Re: If Statement, is there a better way?

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
Highlighted
Partner
Partner

Re: If Statement, is there a better way?

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?

Highlighted
Partner
Partner

Re: If Statement, is there a better way?

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

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Highlighted
MVP & Luminary
MVP & Luminary

Re: If Statement, is there a better way?

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

Highlighted
Contributor

Re: If Statement, is there a better way?

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!

Highlighted
MVP & Luminary
MVP & Luminary

Re: If Statement, is there a better way?

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

Highlighted
Contributor III

Re: If Statement, is there a better way?

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.

Highlighted
Partner
Partner

Re: If Statement, is there a better way?

Nice one as usual Rob.

FYI both download links are downloading qvf now

Highlighted
MVP & Luminary
MVP & Luminary

Re: If Statement, is there a better way?

Thanks for pointing out the download problem. Fixed.

-Rob

Highlighted
Valued Contributor II

Re: If Statement, is there a better way?

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.