9 Replies Latest reply: Sep 16, 2015 8:40 AM by Alisa Lorenz RSS

    Outer Join

    Alisa Lorenz

      Hello everybody,

       

      I’m quite new to QlikView and while programming a dashboard the following question came up:

       

      I have two tables: BSTK and ABK. Both have two colums: BSTK.No, BSTK.Total and ABK.No, ABK.Total.

       

      BSTK ist the order and ABK the confirmation of order. Not every order has a confirmation (see empty fields) and not every order has a value (=0).

       

      I want to make the following inquiry (maybe with an outer join):

       

      If BSTK.Total is empty, then show the value of ABK.Total

      If BSTK.Total is not empty and ABK.Total is not empty, then show the value of ABK.Total

      If BSTK.Total is not empty and ABK.Total is empty, then show the value of BSTK.Total

      If both are empty, then show 0

       

      The results should be shown in a separate Column.

       

      Does anyone have any idea how to solve this? Any help would be much appreciated. Thanks in advance.


      Alicelqv


      BSTK.NoBSTK.TotalABK.NoABK.Total
      A0016840
      A0043230
      A0071310A007138
      A002490A0024915
        • Re: Outer Join
          Manoj Kulkarni

          Why do you need join table with outer condition ? With data provided, we can easily handle it in expression itself

          • Re: Outer Join
            Manish Kachhia
            Temp_BSTK:
            Load
              *,
              [BSTK.No] as Key
            Inline
            [
              BSTK.No, BSTK.Total
              A00168, 40
              A00432, 30
              A00713, 10
              A00249, 0
            ];
            
            
            Join
            
            
            ABK:
            Load
              *,
              [ABK.No] as Key
            Inline
            [
              ABK.No, ABK.Total
              A00713, 8
              A00249, 15
            ];
            
            
            Left Join (Temp_BSTK)
            Load
              Key,
              IF(SUM(ABK.Total) <> 0, SUM(ABK.Total),
              IF(SUM(BSTK.Total) <> 0 and SUM(ABK.Total) = 0, SUM(BSTK.Total),
              IF(SUM(BSTK.Total) = 0 and SUM(ABK.Total) = 0, 0))) as NewTotal
            Resident Temp_BSTK
            Group By Key;
            
            • Re: Outer Join
              Sasidhar Parupudi

              I am assuming that you would want to join the tables in qv and then perform the expressions in the script?

              please provide sample data for the both tables

              • Re: Outer Join
                arjun rao

                Hi,

                Please provide the sample data for two tables and expected results. So that it would be easy to provide the solution from QV experts.

                • Re: Outer Join
                  nagarjuna k

                  Is it compulsory to use Outer Join ? and we can also do other way too

                  • Re: Outer Join
                    Kalyan Navuluri

                    Is this what you are looking for?

                    Capture.PNG

                    BSTK:

                    Load * Inline [

                    BSTK.No,BSTK.Total

                    A00168, 40

                    A00432, 30

                    A00713, 10

                    A00249,

                    ];

                     

                     

                    Left Join(BSTK)

                    ABK:

                    Load ABK.No as BSTK.No,ABK.Total;

                    Load * Inline [

                    ABK.No, ABK.Total

                    A00713, 8

                    A00249, 15

                    ];

                     

                     

                     

                     

                    NoConcatenate

                    LOad BSTK.No,ABK.Total,BSTK.Total,If(IsNull(ABK.Total),BSTK.Total,ABK.Total) as New_Column Resident BSTK;

                    //

                    Drop Table BSTK;

                      • Re: Outer Join
                        Alisa Lorenz

                        Yes, that is exactly the logic I was looking for. Thank you!

                        But how can I make this with variables instead of the fixed numbers (A00713,A00249,...)? Maybe my question was not precise enough, but the list with the data goes on and I want QlikView to always calculate the new column. E.g. when new orders A00534, A00535,... are made and new confirmations come in, I want the programm to automatically update the chart.

                        Maybe you can help me with that, too?

                          • Re: Outer Join
                            Sasidhar Parupudi

                            What is your data source? if it is SQL, replace the inline loads with SQL

                             

                            qualify No Total;

                            BSTK:

                            SQL select No,Total from  BSTK;

                             

                            Left Join(BSTK)

                             

                            ABK:

                            sql select No,Total from ABK;

                             

                            final:

                            Load *,If(IsNull(ABK.Total),BSTK.Total,ABK.Total) as New_Column Resident BSTK;

                             

                            hth

                            Sasi