6 Replies Latest reply: Feb 27, 2015 11:27 AM by bobbyraj santhiogu RSS

    Question about this table join

    Matt Maino

      My qlikview document has a main table being imported from a database and it is called 'loans'. I am also importing a few fields from another database - and these fields reside in several different tables within that database. I am joining these smaller tables to the main loans table by including the account number field and the production date field in all of the tables... If I concatenate all of the smaller tables there will be more than one row for each account number/production date combination in that table. Will this join correctly to the loans table or will there be duplicate records? An example is below:

       

      LOANS:

      Account #DateField1Field2
      55512/311a
      44412/312b
      33312/311c

       

      SMALLER TABLE (after concatenation)

      Account #DateField3Field4Field5
      55512/31a
      55512/31b
      55512/31c

       

      I would like the resulting row to for account '555' to look like this:

      Account #DateField1Field2
      Field3Field4Field5
      55512/311aabc

       

       

      Thank you!

        • Re: Question about this table join
          anbu cheliyan

          You will have duplicates if you join concatenated table directly to Loans table. Try like this

           

          Loans:

          Select * From Loans;

           

          Join(Loans)

          Load Account#, Date, Max(Field1), Max(Field1), Max(Field1) Resident Concatenated_Table Group by Account#, Date;

           

          Drop Table Concatenated_Table ;

          • Re: Question about this table join
            bobbyraj santhiogu

            Hi,

             

            What you need as result is a join not a concatenate.

            Your smaller tables have to be joined instead of concatenate in order to get this:

             

            Account #DateField3Field4Field5
            55512/31abc

             

            then you can join this table with your LOANS table and you'll get your expected final table.

             

            you can also modify your SMALLER_TABLE like this to get the table to join to LOANS:

            SMALLER_TABLE_2:

            LOAD

            Account #,

            Date,

            Field3

            RESIDENT

            SMALLER_TABLE;

             

            JOIN

            (SMALLER_TABLE_2)

            LOAD

            Account #,

            Date,

            Field4

            RESIDENT

            SMALLER_TABLE;

             

            JOIN

            (SMALLER_TABLE_2)

            LOAD

            Account #,

            Date,

            Field5

            RESIDENT

            SMALLER_TABLE;

             

            DROP TABLE SMALLER_TABLE;

             

            Then:

             

            JOIN

            (LOANS)

            LOAD

            *

            RESIDENT

            SMALLER_TABLE_2;

             

            DROP TABLE SMALLER_TABLE_2;

              • Re: Question about this table join
                Matt Maino

                The reason I used concatenate instead of a join is because some of the smaller tables contain the same field names (other than the original two) and i don't want them to join on anything but account number and production date. Any Idea on how to get around this besides naming the fields different things?

                  • Re: Question about this table join
                    bobbyraj santhiogu

                    I'm not sure to have understood what you explained but I think if you keep the second choice :

                    SMALLER_TABLE_2:

                    LOAD

                    Account #,

                    Date,

                    Field3

                    RESIDENT

                    SMALLER_TABLE;

                     

                    JOIN

                    (SMALLER_TABLE_2)

                    LOAD

                    Account #,

                    Date,

                    Field4

                    RESIDENT

                    SMALLER_TABLE;

                     

                    JOIN

                    (SMALLER_TABLE_2)

                    LOAD

                    Account #,

                    Date,

                    Field5

                    RESIDENT

                    SMALLER_TABLE;

                     

                    DROP TABLE SMALLER_TABLE;

                     

                    Then:

                     

                    JOIN

                    (LOANS)

                    LOAD

                    *

                    RESIDENT

                    SMALLER_TABLE_2;

                     

                    DROP TABLE SMALLER_TABLE_2;

                     

                    It should work fine.