topic Re: Linking tables in Data Model in QlikView

Linking tables in Data Model

linoyel — Thu, 07 Aug 2014 11:49:37 GMT

Hi,

I have sales, bonuses and calendar tables.

Sales table holds agent sales data per date. It's linked to the calendar on date field.

Bonuses table holds MONTHLY agent bonuses. It has only year and month fields, but not the date.

I'm supposed to show in a table monthly sales and bonuses for agents.

How am I supposed to link bonuses table to the sales table?

Only by agent_id is not enough, I also have to link some date field in order to link it right to the calendar...

Please help

Re: Linking tables in Data Model

marcus_sommer — Thu, 07 Aug 2014 12:06:51 GMT

Have a look on this thread: Is it possible to run a macro for each row of a chart (table)

- Marcus

Re: Linking tables in Data Model

linoyel — Thu, 07 Aug 2014 12:11:39 GMT

Hi Marcus, I didn't really understand the connection...

Re: Linking tables in Data Model

maxgro — Thu, 07 Aug 2014 12:13:01 GMT

some ideas

group sales by month (if you don't need by date)

or replace month year fields in monthly bonuses with date (1st of month); in the user interface use month for the analysis of bonuses and sales

or http://community.qlik.com/blogs/qlikviewdesignblog/2012/10/26/mixed-granularity

Re: Linking tables in Data Model

joshabbott — Thu, 07 Aug 2014 12:20:19 GMT

It sounds like you'll have to be very careful with this data. From your Sales table, create a key based on the month and year of the date:

autonumber([salesperson] & num(month([date field])) & '|' & year([date field])) as monthlybonuslink

Add this field to the monthly bonus table also:

autonumber([salesperson] & '|' [month] & '|' & [year]) as monthlybonuslink

You will then have a link to the table, but depending how you want to display the bonus amount, you'll need to be careful because there will be a link for every salesperson/date. For instance, if Salesperson A had 10 orders in January 2014, and 1 bonus, if you did a sum on bonus amount, you would get bonus * 10.

Re: Linking tables in Data Model

marcus_sommer — Thu, 07 Aug 2014 12:22:04 GMT

On the from these posting you will find a load-example and a few explanations:

Data:

Load Customer, Date, Sales From Sales;

concatenate

Load Customer, makedate(Year, Month, 1) as Date, Budget From Budget;

- Marcus

Re: Linking tables in Data Model

simenkg — Thu, 07 Aug 2014 12:25:07 GMT

Facts:

load Employee,

Date as %DateKey,

Amount,

'Sales' as FactType

from Sales.qvd (qvd);

Concatenate(Facts)

load Employee,

Makedate(Year,Month) as %DateKey,

Amount,

'Bonus' as FactType

from Bonus.qvd (qvd);

bonus would then be sum({$<FactType={'Bonus'}>}Amount)

sales would be sum({$<FactType={'Sales'}>}Amount)

Re: Linking tables in Data Model

linoyel — Thu, 07 Aug 2014 12:31:17 GMT

Hey Josh, yeah - that's the approach that worked Thanks!

I linked the sales and bonuses tables on 2 fields: agent_id and YearMonth.

In order to get YearMonth I used MakeDate() function in both tables

Regarding displaying sales data it's gonna be sum(sales) and bonus as is (not sum(bonus)).

Re: Linking tables in Data Model

linoyel — Thu, 07 Aug 2014 12:32:24 GMT

Hey Massimo,

Thanks for the answer.

I did replace month year fields in monthly bonuses with date (1st of month) but when I left it as is it didn't get me monthly bonuses if there were no sale on the 1-st of each month.

So, what I did is this making YearMonth fields in both tables using MakeDate() function.

Re: Linking tables in Data Model

linoyel — Thu, 07 Aug 2014 12:56:04 GMT

Actually sum(bonus) worked as well.

It showed just the monthly bonus of an agent and not multiplied monthly bonus, even if there were few orders in different dates by the same agent.

I needed to use sum(bonus) cause I have a set analysis expression: sum({<...>} bonus)

I don't really know why it worked but it worked.

By the way, it there a way to write a set analysis expression but without the "sum"? Something like:

{<...>} bonus

Re: Linking tables in Data Model

marcus_sommer — Thu, 07 Aug 2014 13:09:07 GMT

In my case linking these tables didn't work always properly. Often yes but not always. If some areas have sales and none budget or some have budgets and none sales then this will lead to links against null. Therefore my tables are concatenated and all calculations are fine.

Set analysis couldn't be used without an aggregation function but it could by also only(). In your case I think you didn't need set analysis and a sum(bonus) will work, too.

- Marcus