Recently, I set upon creating an e-mail list for a bi-monthly newsletter that my company Evolcon is going to send with updates on everythings related to Qlik and becoming a more data-driven organization. Of course, I used Qlik to parse and clean the e-mail list, and in the process, I used some not so common scripting functions that are fun to use.
The first step was to extract the e-mail information from Outlook and Gmail. You can easily find sites that will lead you through the process in Google. I used a Google script found on this blog and the Outlook extraction was pretty straight forward.
Parsing with subfield()
Once I extracted all the e-mail addresses from my e-mail and copied them to an Excel file, I started to write my script in QlikView to parse and clean the e-mails. The first challenge I encountered was parsing the domain from the e-mail. That’s a pretty simple job with the subfield() function, and the following function did the job:
SubField(Email,'@',2) as Domain,
Continue reading “Advanced Scripting – Creating an e-mailing list in Qlik”
Life is made up of moments, and I remember clearly the moment I asked by former boss for a career change. Eight years ago I decided to leave a future of implementing ERPs and bet on a new future helping people analyze their data with a software called QlikView.
In 2006, QlikView was new to Mexico and it wasn’t easy to convince prospects that we were better than the OLAP-based competitors that had already been around for decades. However, we knew it was the product of the future because almost every time we competed with OLAP-based software in Seeing is Believing events, we would leave the prospect in awe and win the project. I’ll never forget the time I made a business user shed tears of joy after seeing an analysis in QlikView that the company had never been able to perform after years of numerous, futile attempts with other tools.
I’m more an analyst than a salesperson, so I know QlikView is not perfect. I respect what Stephen Few has done for data visualization and I’ll always have a place in my heart reserved for Tableau, but as I commented in my recent book, I believe QlikView is the best all-around data discovery software. There’s more to data discovery than data visualization. Rapid, efficient and easy data extraction and transformation along with an innovative associative data model are all vital tools to discover data, and this is why I still prefer QlikView.
Now eight after first choosing QlikView, it has been renamed Qlik and as a new, game-changing version is ever closer to being unveiled, I’m going to bet again on Qlik. I’m honored to be among a group of the Qlik Luminaries that includes an impressive list of customers, partners and enthusiasts. As a Qlik Luminary, I promise to take on the responsibility to share how great Qlik is to all those who still suffer from information deficiency, and I also promise to represent the needs of my customers to make Qlik an even greater data discovery tool.
I will keep you updated on what I can share with you during what looks to be an excellent 2014.
AutoNumber() can be a pain when trying to debug problems in a QV data model, especially if you are integrating multiple data sources and need the original source’s keys to trace problems. Sometimes I get the impression that people recommend it based on the idea it will make the links between tables more efficient because numbers are more efficient key fields than strings, but given that all data whether a string or a number value is assigned a binary record pointer automatically, QlikView makes links between tables using those same binary addresses.
Autonumber() does nothing more than slim down the RAM QlikView uses by reducing the size of the symbol table that contains the unique values of a field. Therefore, it makes no sense to transform an ID with autonumber() for a key field and also keep the ID in its original state in another column. The only exception to this rule is when the original ID is used as a dimension or in an expression.
Continue reading “Is AutoNumber() really worth the hassle?”