Cross tables are omnipresent. We just don’t realize that we see them everyday. I am not kidding. If you visit Gallup’s site — I am a political junkie –you will live and breathe with cross tables.
Wether it is the IRS scrutiny of conservative groups or the Benghazi hearings, Gallup survey data is a perfect example of the power of cross table when it comes to research, survey and other forms of statistical data. In its simplest form, cross table shows interrelationship between two variables such as IRS scrutiny of conservatives and public sentiment about it.
While cross tables are essential for researchers, bringing these type of data structures into Qlikview can bring misery as aggregation such as sum, count or average becomes difficult to accomplish.
I encountered such a conundrum recently. A friend of mine — who buys and sells cars — wanted to find a way to bring Manheim Research data showing value index– the premier indicator of pricing trends in the used vehicle market — for last 12 years into Qlikview. Not knowing much about how cross tables work in Qlikview, he was exasperated dealing with years showing as 1995,1996…..2012. Months(Jan… Dec.) and Years(1995….2012) are the two variables in this cross table.
Try to do a set analysis on this kind of data, and you will get the visual picture of the frustration my friend was having with this data. He wanted to create a chart in Qlikview similar to how it has been rendered in Manheim consulting report, and he was ready to throw a towel on Qlikview till cross table swiftly restored his faith in Qlikview’s ability to do miracles.
So, let’s begin — our challenge is to transform the row showing years into a column, and here’s how cross table in Qlikview does it.
CrossTable(Year, Data) LOAD * FROM…….
The top row showing years(1995…2012) will become a column(Year).
Step 1: Create a cross table first.
If you want to create a Year Month column by concatenating newly created Year and existing month column, you will have to use a resident load because year column exists only after the cross table gets created in Qlikview first.
Step 2: Drop original table(CarSales).
- Drop Table CarSales;
That’s it.
Elsewhere:
Sometimes Fat is Better via Living Qlikview
