4 Replies Latest reply: Jun 1, 2012 11:12 AM by Juan Navarro RSS

    Best way to create a relation between tables

      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

          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

            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:

             

            weekyeargroupsum ordersplan
            26001500
            172012A1300500
            172012B13001000

             

            Regards.

              • Re: Best way to create a relation between tables

                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

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