Best way to create a relation between tables

[#1]

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

[#2]

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.

[#3]

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.

[#4]

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

[#5]

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))