topic Best way to create a relation between tables in QlikView

Best way to create a relation between tables

Thu, 31 May 2012 14:00:22 GMT

hi,

I have 2 tables, one with all the orders in it, and the other for each week it goal.

Each order has a group and each group has for each week a target(goal).

But how do I link those 2 in qlikview?

table with orders:

week year group profit

17 2012 A 500

17 2012 B 600

17 2012 B 700

17 2012 A 800

table with targets;

week year group goal

17 2012 A 800

17 2012 B 1000

and as result I seek is:

week year group profit goal

17 2012 A 1300 800

17 2012 B 1300 1000

the table with targets can be changed because its in excel and its static.

Kind regards,

Thijs

Best way to create a relation between tables

Thu, 31 May 2012 14:57:40 GMT

He Thijs,

You could do a join between the target and orders table. You don't need to specify the key, QV will automatically do the join on week,year and group. But first you have to do an aggregate on the order table. Something like group by week year group and sum(profit) as profit.

Another option is to do nothing. Because QV automaticly makes a link between tables with the same column names.

Best way to create a relation between tables

Thu, 31 May 2012 17:30:51 GMT

You must concatenate the orders with the target but in the target table add a column and put something like "target" then on the chart (straight table) add the three dimensions week, year and group and in the expresion add two, one with the sum of profit when cames from the orders: sum(if(target<>'target',profit)) and another one when cames from the plan sum(if(target='target',profit)) you should have something like this:

week	year	group	sum orders	plan
			2600	1500
17	2012	A	1300	500
17	2012	B	1300	1000

Regards.

Re: Best way to create a relation between tables

Fri, 01 Jun 2012 06:49:29 GMT

I did now only a relation between the groups and this as sum:

sum({$<weekTarget={$(=Max(weekOrder))},yearTarget={$(=Max(yearOrder))}>}BUDGETTarget)

And it seems to work, is it correct?

quick side question:

In the real situation I have another table, the table PART and there it stored the group, and in the Order table it has only the part.

So i already linked those 2, to get the group and it works fine, but some orders don't have parts that exicst in the PART table.

So the group is a dash ("-") and I can't filter them in a group called "additional".

I used this code before loading.

if(IsNull(group) =-1 or Trim(group) = '', 'additional',group) as headgroup

Re: Best way to create a relation between tables

Fri, 01 Jun 2012 15:11:43 GMT

It seems that could work as you said, and I havent use the isnull validation but i guess it could work that way, in the first example what I did was:

Orders:

LOAD * INLINE [

week, year, group, profit

17, 2012, A, 500

17, 2012, B, 600

17, 2012, B, 700

17, 2012, A, 800

];

Concatenate

TARGET:

LOAD * INLINE [

week, year, group, profit, target

17, 2012, A, 500, target

17, 2012, B, 1000, target

];

and in the graphic I use the next expressions:

Order

sum(if(target<>'target',profit))

Plan

sum(if(target='target',profit))