2 Replies Latest reply: Dec 11, 2014 4:50 AM by alexandra burovik RSS

    help to find best join scenario

      Hello!

      Could someone help me to find the best way of joining to tables?=)

      I've got two tables, like

       

      [A,B,C,D,E,F

      1,2,3,4,5,6

      5,4,1,2,3,8

      4,1,2,7,8,9

      7,8,9,6,5,4

      ];

       

      [A,B,C,D,E,F

      1,2,3,4,5,p

      5,4,1,2,w,e

      4,1,2,7,8,9

      7,8,9,6,5,4

      ];

      and as a result I need

      1,2,3,4,5,6

      5,4,1,2,3,8

      5,4,1,2,w,e

      4,1,2,7,8,9

      7,8,9,6,5,4

      So if only one last field differs, Qlik should take the value from first table, and if two fields differ, it should take BOTH lines to teh resulting table. practically my second table is a result of group- i'm calculating totals and if there was no total in the first table, I need to put it there, but if there already was then its in priority

      Thanks in advance!

        • Re: help to find best join scenario
          Sushil Kumar

          Hi,

           

          Attached is solution code for your problem.

           

          HTRH

          Sushil

            • Re: help to find best join scenario

              @Sushil Kumar, thank you for your answer!!

              could you, please, advice me, how to combine it with group dy, made in load of 2nd table? 'cos I'm receivind invalid expression error, as expected...

              Actual script is as follows:

               

              Sales_plan:

              load

              SalesOffice,

              State ,

              Channel,

              Production_Direction ,

              TradeMark,

              Product_Type ,

              Amount,

              Year,

              Month,

              if(len(trim(Group))=0,'All '&Type,Product_Type) as Product_Group,//if its an aggregated plan already

              from sales.qvd (qvd);

               

              Sales:

              load

              SalesOffice,

              State ,

              Channel,

              Production_Direction ,

              TradeMark,

              Product_Type ,

              Amount,

                Year,

              Month,

                sum(Amount) as Amount,

                 'All '&Type as Product_Group

              resident Sales_plan Group by Direction,TradeMark,Type,SalesIffice, State,Channel;