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
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
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!
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.
Finally was able to open and this works great! I actually have 6 variables I'm using in place of ifStatements and/or pick(match()). Everything works correctly and I did see some performance increase. Unfortunately I'm loading 7-8 million row and then doing an aggr calculation on that table which ends up being around 6 million rows, so it does take longer than I would like to load. I've also noticed on my dashboards that where I'm using more granular data that it takes much longer to load. Does anyone have any suggestions? I would like to improve this, as I'm only pulling 6 months worth of data and would like to ramp that up to a rolling 12. Thanks everyone for your help!