Chapter 9. Basic Data Transformation

At this point in the book, we've already covered topics related to data sources such as extraction, data visualization, scripting, and data modeling. These topics are all interconnected in the development process. We will now complement these four topics with a fifth subject that is of fundamental importance, and one that plays an essential role when developing QlikView apps, taking to an advanced level the lessons learned from all of the previous chapters: Data Transformation.

The topics we'll cover here will help us:

• Make the data sources adequate to meet our data model design requirements

• Deal with unstructured tables (such as Crosstables) and incorporate them into our data model

On we go.

Changing the source table structure

We've seen how the QlikView engine works and the importance of having a data model design that fully takes advantage of QlikView's associative algorithms. So, the first section of this chapter deals with transforming source tables to make them adequate for our data model. The different structure transformations we'll make are:

• "Cleansing" a dirty table

• Converting a Crosstable to a standard table

• Using hierarchy tables

• Loading generic tables

"Cleansing" a dirty table

As we've said before, it's not that uncommon for business users to require consolidated information from all sorts of different sources: the CRM, the company's Data Warehouse, Excel tables, Legacy systems, and so on. In these scenarios, the developer commonly faces the challenge of adapting a user file (Excel, CSV, TXT) that has either a non-standard structure or contains "dirty" data which needs to be removed, such as report headers or subtotal lines, and sometimes both.

Fortunately for us, QlikView...

Summary

We have seen four different scenarios in which the source table is not suitable for a QlikView data model, and we have shown the tools QlikView provides to deal with those formats. We have learned how to use the Transformation Wizard to remove garbage from input tables, fill missing cells, and unwrap table files.

We also learned what a Crosstable is, why it's not fit for the QlikView data model, and how to transform it into a traditional table. We saw how to deal with hierarchical tables and identify parent and child nodes.

Finally, we learned what a generic table is and how to take advantage of QlikView's ability to transform its structure.

In Chapter 12, Advanced Data Transformation, we will look at more advanced techniques for transforming source data for use in different data model designs.