Merging data from different tables

[#1]

Hi All,

I just attended a hands on workshop for Qlik and I'm really impressed with it.

I'm working to create some demo applications to show others at my company.

One thing I'd like to do is show sales data by sales rep.

Currently we have a report that shows several key data points for a specific sales rep all on one line.

To keep it simple, lets say that for each sales rep, I want to show Open Order Value and MTD Sales.

These data points would come from two different sets of tables in our database.

In our report, we have a complex query that uses sub queries to pull and summarize all the data points so I know I could just reuse that query but if I want the Qlik application to be more dynamic, for example being able to change the month or specify a data range, the application would need access to the detail records and not just the summary.

I've attached a screenshot that shows part of our current report.

How would I achieve this in Qlik? Let's say that I want a straight table chart to display this.

What I envision is that this would be at the top of my sheet and then as you click each sales rep, there would be other sheet objects that show related details and breakdowns.

[maxgro]

about merging data from different (facts) tables

http://community.qlik.com/blogs/qlikviewdesignblog/2013/05/17/concatenate-vs-link-table

Re: Link table v Concatenate for fact tables

[erikzions]

Load the unsummarized data straight from SQL, then summarize it in a chart. 

It would also be easier if you could include a sample.  If you are worried about sharing private data, you can scramble fields in the document properties. 

[#4]

Hi Erik,

Thank you for the response. You should find a screenshot attached to my original post that shows part of what our current report looks like.

[maxgro]

from your image we can guess you have some fact tables

- a budget table (by month)

- a sales table (by date)

- perhaps some other one

- a rep dimension

one way could be to concatenate (similar to union in sql) the fact tables and add some common dimensions, pehaps rep and a calendar (search master calendar)

[erikzions]

I did see the example.  I just meant that if we could see what the underlying data was like I could help design the chart.  As it is I can only tell you to load your data at the sales level.  Create a chart and use your rep as the dimension. Then summarize the expressions for each metric you want.  Something like sum(sales).  Then with proper filters you can make it more dynamic.  If you summarize the data before Qlikview gets it you cannot achieve as dynamic a solution.

[#7]

Thanks Massimo and Erik.

Massimo, you are correct with the inferred fact tables.

I've attached the design of the underlying SQL View that powers my report. (I hope it comes through ok)

It is a series of sub queries that summarize the data needed.

The order information comes from two tables that are joined which represent the Order Header (OrderHed) and Order Lines (OrderDtl).

The sales data comes from two tables that are joined which represents the Invoice Header (InvcHead) and Invoice Lines (InvcDtl).

The Budget data is stored in a table that has monthly budget numbers for each sales rep.

Let me know if you need more information and thanks again for the help so far.

[#8]

Hello,

I know people are busy doing their day jobs but I'd thought I would just ping this discussion.

Please let me know if you need more detail.

Thanks again for all the help so far.

[maxgro]

so you have

- order lines (+ header)

- invoice lines (+ header)

- budget

2 dims

- date (for orders and sales a date, for budget a month; I think it could be a date, the first of the month, easier)

- rep

in qlikview I will probably join order lines and header, then invoice lines and header and then concatenate these two tables with budget; one fact table, two dims