Advanced Scripting – Creating an e-mailing list in Qlik

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”

Thoughts on being a Qlik Luminary

2014 Qlik Luminary Digital Badge

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.

Let’s do it again! Win a Free Copy of Packt’s Learning QlikView Data Visualization.

Packt Publishing is going to be giving away 2 free copies of Learning QlikView Data Visualization this week.


Overview of Learning QlikView Data Visualization

  • Explore the basics of data discovery with QlikView
  • Perform rank, trend, multivariate, distribution, correlation, geographical, and what-if analysis
  • Deploy data visualization best practices for bar, line, scatterplot, heat map, tables, histogram, box plot, and geographical charts

How to Participate
During the
last giveaway, I asked for ideas on what data visualizations you would like to add to QlikView.  Now, I can’t imagine the development team at QlikTech being able to focus on creating and improving every chart and graph in QlikView, so let’s help them focus on the best data visualization techniques, and therefore leave behind some of the charts and graphs in QlikView that don’t do a good job of helping us discover data.

Continue reading “Let’s do it again! Win a Free Copy of Packt’s Learning QlikView Data Visualization.”

Is AutoNumber() really worth the hassle?

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?”

Congratulations to the winners!

Thanks to all those who responded to my post in QlikCommunity and in this blog about what data visualization they would like to see in QlikView.  I learned a few things about what people would love to do in QlikView.  Most visualizations that were posted are possible to create in QlikView, but what frustrates many users seems to be the difficulty creating these visualizations through tricks, extensions or third-parties.

I think we can expect great advances in data visualization in QlikView after the company bought NComVA, a company dedicated to data visualization.  Along with QlikView’s continuous improvements, I believe it is important to continue creating new and improving existing extensions.  There is always going to be a visualization that QlikView cannot create out-of-box, and that’s why extensions are such a key element.

So, enough talking.  The winners of the three Learning QlikView Data Visualization e-books are:

  • Philipe Grenier
  • Rebeca Gums
  • Sokkorn Cheav

If you are a winner please send me a message in QlikCommunity with your e-mail address so that I can send you your e-book.

Thanks again for all those who participated.

Hope to see you around,


Win an e-book copy of Learning QlikView Data Visualization

This week I’m giving away three e-book copies of Learning QlikView Data Visualization.  To be eligible to win all you have to do is post what data visualization method you would like to do in QlikView that you have not been able to do until now.   It could either be a chart or a technique.

For example, I would like to do be able to do scatterplot matrices like the one below.


Post your idea in LinkedIn, Twitter, Google+, or Facebook.  You can either respond to my post or create your own post mentioning me.

Don’t forget to include a link that explains the data visualization method you would like to do in QlikView.

Please respond by Monday, Nov. 4th and I will announce the winners on Wednesday, Nov. 6th after an old-fashioned drawing.  Soon after the e-books will be delivered to the winners’ e-mails as a Kindle e-book.

You don’t need a kindle to read the e-book.  You can download the kindle application from and view it from your laptop or iPad.

Good luck!


Pie charts or parts-to-whole analysis

Those who have read Learning QlikView Data Visualization may wonder why I didn’t talk about pie charts in the book.  Some may even assume that I am a data visualization idealist that tries to deny people’s temptations to create pie charts, but while I do support those idealists, I have created more than a few pie charts in my eight years working with QlikView.

I have strived to create alternate charts that more effectively show a parts-to-whole relationship, but some users with not accept any other chart that is not a pie chart.  So, inevitably pie charts sometimes end up in my QlikView applications.

Continue reading “Pie charts or parts-to-whole analysis”