4 Replies Latest reply: Jan 5, 2010 6:23 PM by Javier Silvio RSS

    Joining more than two tables

    Javier Silvio

      Hi everybody,

      I need some help in how to join more than two tables. I need to, for example:

      1-. Create a table with the inline assistant, consisting of USERNAME and ACCESS.

      2-. JOIN that table with various others from, say an Excel file, which contains the passwords. Imagine I need each user of the document to renew his/her password through an excel file which must only be accessed/modified by them.

      I tried that operation and it seems to be working for the first JOIN, but then; when I call the other tables, they just don't join as I was expecting. The result it's something as if I was concatenating them.

       

      This is the code:

       


      Accesos:
      LOAD * INLINE [
      ACCESS, USERNAME
      ADMIN, ADMIN
      ADMIN, SA
      USER, SA
      USER, AA
      USER, BB
      USER, CC
      ];
      ODBC CONNECT TO [Excel Files;DBQ=C:\Documents and Settings\XXXX\My Documents\Cambio de Password Usuario de QV Client MAT.xls];
      OUTER JOIN (Accesos)
      Load USERNAME,
      [PASSWORD]
      FROM [C:\Documents and Settings\XXXX\My Documents\Cambio de Password Usuario de QV Client MAT.xls] (biff, embedded labels, table is [ActPasswords $]);
      OUTER JOIN (Accesos)
      Load USERNAME,
      [PASSWORD]
      FROM [C:\Documents and Settings\XXXX\My Documents\Cambio de Password Usuario de QV Client MAT.xls] (biff, embedded labels, table is [ActPasswords2 $]);


       

      The result:

       

      USERNAMEACCESSPASSWORD
      AAUSER0
      ADMINADMIN
      BBUSER
      CCUSER
      CC0
      SAADMIN
      SAUSER


       

       

       



       

       

       

       

       

       

       

       

       



       

       

       

      See what I mean?

      The first excel sheet contains the password (0) for the user AA. So the script is doing what I want: Add the password from that excel sheet.

      But for the "CC" user, the script is just adding a new row containing the password. It's not joining the information with the password to the "Accesos" table.

      I hope someone can help me, It seems to be a database 101 lesson, but I'm pretty new with this stuff. Maybe I'm not using the right sentence, but I've tried with all kind off JOIN, INNER, KEEP, etc.

      Thanks in advance,

      Javier

       

       

       



       

       

       

       

       

       

       

       

       



       

       

       

       

       

       

       



       

       

       

       

       

        • Joining more than two tables

          pretty new to Qlikview myself, but I'm pretty sure you can just drop the 'join' part alltogether. QlikView is going to concatenate all of your password files together (because they have the same number of fields, and each field has the same name), and then join them by name to your user table (assuming the field names are the same). You shouldn't need to explicitly join anything.

            • Joining more than two tables
              Michael Steedle

              Fry,

              The Access table has a different structure than the password spreadsheets, so any concatenation would have be explicit, using the CONCATENATE load statement prefix.

              We need joins here, anyway. Concatenation would add rows to this table, but we want the same number of rows as the original Access table inline load, only with the addition of a field containing password values.

            • Joining more than two tables
              Michael Steedle

              You don't want to be executing two separate joins here, and the use of outer joins, specifically, is how you are ending up with multiple rows per user. You may want to read about what outer joins are, but your first one is adding the password column to the Access table, and the second one is doing something else.

              Concatenate load all of your spreadsheets into one temp table, then left join that temp table to your Access table. You can then drop the temp table.