Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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'))))))))))))))))))
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/
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?
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
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/
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!
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
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.
Nice one as usual Rob.
FYI both download links are downloading qvf now
Thanks for pointing out the download problem. Fixed.
-Rob
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.