10 Replies Latest reply: Mar 1, 2011 8:00 AM by Ashwin Pagare RSS

    Tables join

      Hi All,

      My problem is Suppose I have two tables :

      Table A :

      ID Amount

      ID_1 2

      ID_2 10

      ID_3 8

      ID_4 3

       

      Table B:

      ID Amount

      ID_5 12

      ID_2 11

      ID_7 3

      ID_4 7

       

      In the output i want only :

      ID

      ID_5

      ID_7

       

      I know its very simple, but i m just screwed and not getting solution for same. Please Help on the same

        • Tables join

          Hi, try

          TableB:

          Load

          ........

          From .....

          Where NotExists(ID)

          Good luck!

          Rainer

          • Tables join
            Miguel Angel Baeyens de Arce

            Hello,

            I don't know if I got your requirement right, but if you only want to keep in table two those values that don't appear in table one, you can use the following

             

            TableA:LOAD ID, AmountFROM TableA; TableB:NOCONCATENATE LOAD ID, AmountFROM TableB WHERE NOT EXISTS(ID);


            Hope that helps

              • Tables join

                I think i was not able to explain the problem correctly.

                See Suppose i have same above two tables A and B in excel.

                Now my report will be showing all those ID which in table B but not present in tables A. Actually they will be working as the NEW ID's.

                OUTPUT :

                ID Amount

                ID_5 12

                ID_7 3

                  • Tables join
                    Miguel Angel Baeyens de Arce

                    Hi,

                    So I see several things here.

                    If possible, I'd create a flag in the script so you can easily identify which IDs in Table B are nor present in Table A (note that since I'm loading from INLINE I have to rename fields ID to IDA or IDB, and so with AmountA and AmountB, but loading from excel may not need that, since the IF() conditional would be built runtime.):

                     

                    TableA:LOAD * INLINE [IDA, AmountAID_1, 2ID_2, 10ID_3, 8ID_4, 3]; TableB:NOCONCATENATE LOAD * INLINE [IDB, AmountBID_5, 12ID_2, 11ID_7, 3ID_4, 7]; TableBWithFlags:LOAD *, IF(NOT EXISTS(IDA, IDB), 1, 0) AS FLAGRESIDENT TableB; DROP TABLE TableB;


                    Now in your chart, the following expression should work:

                     

                    Sum({< FLAG = {1} >} AmountB)


                    But what I think, provided you have QlikView version 9 or higher, using indirect set analysis with elemental functions will do it cleaner and probably faster:

                     

                    Sum({< IDB -= P(IDA) >} AmountB)


                    Hope that helps.

                • Tables join

                  Hi,

                  just modified Miguels statement.

                  TableA:
                  LOAD ID, Amount
                  FROM TableA;

                  TableB:
                  NOCONCATENATE LOAD ID, Amount
                  FROM TableB WHERE NOT EXISTS(ID);

                  Drop TableA;

                   

                  That should work.

                  Rainer

                  • Tables join

                    Hi

                    Use right join... Thn u wil get the answer what u need

                    • Tables join
                      Pranav Shandilya

                      A:

                      LOAD ID as ID1, Amount FROM [C:\Documents and Settings\rlimumweb011\Desktop\table A.xls] (biff, embedded labels, table is [Sheet1$]);

                      B:

                      LOAD ID , Amount FROM [C:\Documents and Settings\rlimumweb011\Desktop\table B.xls] (biff, embedded labels, table is [Sheet1$])

                      where Not exists(ID1,ID);

                      Drop table A;

                      As told by my seniors(Bhaskar )