topic Re: Displaying data from two tables in one graph in QlikView

Displaying data from two tables in one graph

Fri, 31 Jul 2015 09:22:25 GMT

Hello!

I have two table with matching fields. And I need to represent them both in a chart if possible(sum of Sales per per week). Both table consist of some figures per category oevr a period of time but they can not be merged. The should be combined into one graph to provide a visual comparison of the actual sales and budget targets. Maybe someone can assist with this. Currently I have two separate graphs showing the calculations per week. Also, Table2 consists of only current year figures (so I need to display only this years data); budget is estimated for each month of the year and actual sales table consists of only data starting from the beginning of the year until current week. Examples of the table sare presented below.

Table1-Actual Sales

Item	Category	EUR	Year	Month
ItemA	Category1	209,40	2014	1
ItemB	Category2	500,17	2015	2
ItemC	Category3	315,82	2015	1
ItemD	Category2	740,11	2014	2
ItemE	Category2	599,51	2014	3
ItemF	Category3	880,90	2015	2
ItemG	Category2	772,05	2015	1

Table2-Budget

Category	EUR	Year	Month
Category1	529,85	2015	1
Category2	874,50	2015	2
Category3	508,78	2015	1
Category1	830,31	2015	1
Category2	359,12	2015	2
Category3	432,19	2015	3

Re: Displaying data from two tables in one graph

avinashelite — Fri, 31 Jul 2015 09:31:57 GMT

Since the data cannot be combined, try to build a composite key between the two tables , that will server the purpose I guess

composite key like :

Actual_Sales:

Load

Category&'-'&Year&'-'&Month as Key,

....

Budget:

Load

Category&'-'&Year&'-'&Month as Key,

....

This will give the desired result

Re: Displaying data from two tables in one graph

Gysbert_Wassenaar — Fri, 31 Jul 2015 09:34:03 GMT

Table2-Budget

Category	EUR	Year	Month
Category1	529,85	2015	1
Category2	874,50	2015	2
Category3	508,78	2015	1
Category1	830,31	2015	1
Category2	359,12	2015	2
Category3	432,19	2015	3

Why do you have two rows for the same Category for the same period of the same year? Which one has the correct budget for the category?

Re: Displaying data from two tables in one graph

puttemans — Fri, 31 Jul 2015 09:44:48 GMT

How about loading your budget figures into your sales table?

map_budget:

MAPPING LOAD
Category&Year&Month,
EUR
FROM
your budget table

Sales:
LOAD Item,
Category,
EUR as Sales,
Year,
Month,
Applymap('map_budget', Category&Year&Month) as Budget
FROM
your sales table