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);