Thursday, November 19, 2009

Understanding Join and Concatenate

The Qlikview script functions JOIN and CONCATENATE can sometimes be used to tackle the same problem, but there are important differences that should be understood.


Examine the sample tables below. Note that they share one common field name, "Key". Also note that Table1 has a Key value "3" that is not present in Table2.







JOIN will combine rows where the Key value matches. The keyword OUTER will also retain rows that do not match rows in the other table. Here's what the merged table will look like after an outer join.

OUTER JOIN (Table1) LOAD * RESIDENT Table2;


Values A1 and C1, which were in different tables, now occupy the same row in the result table. The row with Key 3 has missing values for C & D, because there was no matching Key in Table2.






Creating a chart that uses "Key" for dimension will produce results similar to the Table Box above.





The important point is that values with the same Key value have been merged together into a single row. If value A1 is selected, note that values C1 & D1 remain associated (white). The set A1,B1,C1,D1 is indivisible.



Now let's look at Concatenate. Concatenate appends the rows of one table to another. Concatenate never merges any rows. The number of rows in a concatenated table is always the sum of the rows from the two input tables. Here's what our sample data will look like after Concatenate.

CONCATENATE (Table1) LOAD * RESIDENT Table2;


Rows with like Key values are not merged together. The rows from Table2 are simply appended to Table1. Because the tables have different fields, rows will have null values for the fields from the "other" table.





If the data is used to build a chart that utilizes the common field "Key" as dimension, the chart looks just like the JOINed table.




Let's make the selection "A1" in Field A and see it's impact on our visible charts and tables.


When A1 is selected, the association to C1 & D1 is lost and C/D values become null in both the Chart and Tablebox. We cannot select both A1 and C1. This is a different result than the JOINed example.


Let's consider a more realistic example where we may choose between JOIN and CONCATENATE. Consider the two tables below. Note that only one BudgetAmount row is present for each Region-Year combination. In the Sales table, the SalesAmount is broken down by Department within Region.

If we load both tables we can produce a chart using expressions like =Sum(BudgetAmount).


The Budget and Sales values have been summed correctly.



We then notice that we have an undesirable synthetic key, created by the Budget and Sales tables sharing the Year and Region fields. One approach to eliminate the synthetic key would be JOIN or CONCATENATE. But which one in this case?







Let's try JOIN and see what the Chart looks like.

OUTER JOIN (Budget) LOAD * RESIDENT SALES


The summed Budget numbers are incorrect!


A look at raw data of the joined table will identify the problem. The JOIN repeated the BudgetAmount value on each Department row.


Let's try with CONCATENATE.


CONCATENATE (Budget) LOAD * RESIDENT Sales;


The numbers are now correct and we've accomplished the goal of eliminating the synthetic key.





A peek at the data in the Concatenated table will make it clear why the chart is now correct. There is only BudgetAmount value or each Year-Region.


JOIN and CONCATENATE are both very useful and frequently used functions in Qlikview. It's important to understand the differences between them.

-Rob

Sunday, November 15, 2009

Customer Portal Delivers

I've blogged in the past and buttonholed Qliktech employees about the need for greater visibility of bug tracking and enhancement requests. The new Customer Portal delivers just what I was looking for and more.

The Customer Portal is available from a tab on the www.qliktech.com or QlikCommunity web sites. The Portal uses your existing Qlikview userid (SSO) and provides a link to register if you are new.

In addition to online support case submission and tracking, the Portal offers:
  • Searchable database of issues and solutions reported by other customers.
  • Submission of "ideas" (enhancement requests). You can also vote and comment on ideas.
  • Technical papers and how-to videos.
  • License inventory for your site.

I've found the Portal to be easy to navigate and the response good. It's so easy I don't have any tips to offer. I like the PDF viewing feature in the Content section and wish we could do the same for videos.

I encountered one small glitch in my initial login to the Portal. I was already logged in to QlikCommunity and entered the Portal from the tab. The Portal declared that my password was expired and required me to change it. The result was that my QlikCommunity and Customer Portal passwords were no longer synchronized. I rectified the problem by changing my password in QlikComuunity to match.

Content is still being moved in from the old locations. It may be a few more days before all the content is there. As is frequently the case in migrations, some text formatting and history is lost when content is moved from old system to the new. For example, most of the "ideas" look like they were submitted by a single user. I'm guessing this is the user assigned to copy the entries over from the existing "Enhancement Requests" database.

All in all, an exceptional resource for Customers. Thanks to the Qliktech team for getting this in place.