4 Replies Latest reply: May 10, 2012 3:03 AM by Anosh Nathaniel RSS

    Join temp tables

    innessa2

      Hi,

       

      I am trying to create the following statement with temp tables in qlikview.

       

      Here are my temp tables:

       

      ProductName:

      LOAD *, ResourceID as Value                                                                     
      RESIDENT ProductNameTemp;
      DROP TABLE ProductNameTemp;


      Users:
      LOAD *, ResourceID_1 as Value_1                                                         
      RESIDENT UsersTemp;
      DROP TABLE UsersTemp;

       

       

       

      I would like to create:

      Join_1:

      Load ResourceID_1, Netbios_Name0, User_Name0, ProductName resident Users

      where ResourceID_1 not in (select ResourceID from ProductName);

       

       

       

       

      Please help.

      Inna

        • Join temp tables
          Anosh Nathaniel

          Hi Inna,

           

          Use the not exist clause of qlikview.

           

          While loading data for Join_1 use the below syntex:

           

          Join_1:

          Load ResourceID_1, Netbios_Name0, User_Name0, ProductName resident Users

          where not exist(Value, ResourceID_1 );

           

           

          Hope this help,

          Anosh

            • Join temp tables
              innessa2

              Hi Anosh,

               

              How do I pass through where not Exists in ProductName table.

               

              ProductName:
              LOAD *, ResourceID as Value
              RESIDENT ProductNameTemp;
              DROP TABLE ProductNameTemp;


              Users:
              LOAD *, ResourceID_1 as Value_1
              RESIDENT UsersTemp;
              DROP TABLE UsersTemp;

               

               

              ProductName1:
              Load ProductName, LastUsage, '' as Netbios_Name0, '' as User_Name0, ResourceID
              resident ProductName
              where not Exists(Value, ResourceID_1);

               

              Users1:
              Load ProductName, '01/01/1981' as LastUsage, Netbios_Name0, User_Name0, ResourceID_1 as ResourceID
              resident Users
              where not Exists(Value_1, ResourceID);

               

               

              Cheers,

                • Join temp tables
                  Anosh Nathaniel

                  Hi Inna,

                   

                  I am not able to understand what you want to do. As per my understanding first you want to load ProjectName and User tables without any where clause. Then why do you again want to load productName table with where not exist clause.

                  As per documentation the exist clause syntex is:

                  exists(field [ , expression ] )

                  Determines whether a specific field value exists in a specified field of the data loaded so far. Field is a name or a string expression evaluating to a field name. The field must exist in the data loaded so far by the script. Expr is an expression evaluating to the field value to look for in the specified field.

                   

                  Please explain in detail what do you want to do.

                   

                  Regards,

                  Anosh

              • Re: Join temp tables
                wishes29

                This will work

                Join_1:

                Load ResourceID_1, Netbios_Name0, User_Name0, ProductName resident Users

                where not exists (ResourceID,ResourceID_1);