Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
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
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))