QlikView has extremely powerful load script syntax which in 99% of the cases will solve every problem you have or might encounter. But sometimes you run into one of those edge cases where the standard load script syntax is not enough or the end result would be un-maintainable.
I recently ran into one of those cases when I was analyzing first names in the US. Typically with both first and last names you have many different variations of the same name.Take Susanna for example, in Sweden it’s probably Susanne or in Russia Syuzanna. Although spelled differently they do belong to the same family. But how should I group them together in QlikView?
An unlikely hero to the rescue
I settled on implementing a SoundEx algorithm, http://en.wikipedia.org/wiki/Soundex, which is commonplace in most database software nowadays. However, being somewhat lazy I quickly realized that replicating the algorithm using load script syntax would take me forever so I settled on something different.
A Google search later I had discovered the SoundEx algorithm implemented in JavaScript, http://creativyst.com/Doc/Articles/SoundEx1/SoundEx1.htm#JavaScriptCode.
Perfect, just what I needed!
The QlikView macro module supports both VB and JScript (Microsoft’s variant of JavaScript) so I could easily copy and paste the algorithm as a new macro module in QlikView. Now from my load script I can call that module during reload as such,
Load
Name,
//Call macro module SoundEx() – returns soundex code.
SoundEx(Name) as SoundEx
From …
Great! Now all the loaded names have a SoundEx code attached to them which would allow me to count the names within those groups instead of only basing my analysis around the count of a single name.
This was just one use-case for how you could leverage macro modules during reload.
Seeing as JavaScript also has implemented regex you could also leverage macro modules to do phone number or address validation. With the recent explosion of open source JavaScript code on the web, chances are someone before you already built a module of what you are looking for.
A final word or warning, do exercise caution and don’t push all your logic into macro modules just because you can.
Also before you consider using macros for the front end, read this excellent post by HIC http://community.qlik.com/blogs/qlikviewdesignblog/2014/04/28/macros-are-bad
