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.
//Call macro module SoundEx() – returns soundex code.
SoundEx(Name) as SoundEx
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.