Adding an additional "detail" table to an existing dashboard without breaking it

[#1]

Did you ever complete a dashboard with all the charts and objects that count rows in your load and then someone comes along and asks you for additional data to be related to your load.  You have already defined in a database view all of the data you will want to display on all the charts based on that database view.  You already know that the relationship of the introduction of a new table in a load script (joining to that view) will explode the number of rows of your result set when you join them.  Your only goal is to maintain the existing counts in the load of your primary view and not increase them by the join of the new table in its one to many relationship to your view.  Some reporting tools could handle such a scenario in the design of their data model but I wonder if QlikView has any such modeling scenario to display this "new detail data" from the new join in a data model without increasing the number of rows in the load of the primary view as being displayed on the charts.  I would expect not from what I have read about it thus far, doubtful... but I just needed to ask.

The goal is to maintain the original row counts of the original view for all of your existing display objects on the dashboard and to see the (additional) detail rows of the new table in a separate (detail only) box.

Thanks!

[marcus_sommer]

I think you need to elaborate it in more details (screenshot from tableviewer, script, number of records and so on) what do you are trying to achieve - whereby for me it sounds that you want to transfer a sql-datamodel from a DWH or another BI tool into qlikview. This is of course possible but thinking in this "old" structures won't be very helpful - many things are different and easier in qlik.

- Marcus

[#3]

HI Marcus,

The table viewer translates to a single table.

Select * from table

The table is actually a view that joined three tables but doesn't matter.  We will simplify this scenario to only one table since that is the model shown in a table viewer.

Several list boxes provide dimensions which are all charted by these dimensions.  Let's consider for a moment there is one row of data per client in this table.  The count of rows in the data set is perfect and counts are shown in objects on the dashboard.  There might even be some rolled up money amounts per client row of data which could be summed by a dimension of time.

Now, let's consider adding a telephone table to each client row by joining to the unique client id.

As you know, we have a home phone, a work phone, and a cell phone in our phone table, this client's data set will now triple by the number of rows when we make this join.

Let's assume each row of this original table contains the sum of purchase.  Any additional telephone numbers joined to this client will multiply the rows for this client by the number of phone numbers this person has.

We now get a request to add the detail of phone numbers per client without causing a cartesian on those unique client rows that already contain rolled up numbers provided in the original data set source.  I have reduced this sample data set (actually a view) to a single table for this scenario.

Now for the big question, is there a way to create a visual display of the phone numbers per client without causing a cartesian in the display of distinct row counts and financial totals per client.

The goal is not to recreate any objects on the dashboard.

It's simply a matter of reporting and displaying different levels of grouping as in most common reporting tools that allow multiple levels of nesting grouped data.

I do not believe QkikView provides a solution for this but would be happy to be proven wrong.

[marcus_sommer]

The qlik standard-solution would be to just load these telephone-table and associate it with your main-table over your key-field [client id]. There is no join necessary between them and an association of 1:n between the tables worked well and returned the right results.

- Marcus

[#5]

Thanks Marcus!

Well as things have it.  A synthetic index was created and the count per index number failed to respond to the count function.  So I pointed that to a different coluMN of that main table and no charts were affected.  The new table attached displays properly and of course it's count of rows are more than the main table.  It's all good.  Thanks for the assurance.  Only minor adjustments.