topic Join temp tables in QlikView

Join temp tables

Anonymous — Wed, 09 May 2012 04:05:43 GMT

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

Wed, 09 May 2012 04:32:21 GMT

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

Anonymous — Wed, 09 May 2012 07:15:23 GMT

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,

Re: Join temp tables

Wed, 09 May 2012 10:48:07 GMT

This will work

Join_1:

Load ResourceID_1, Netbios_Name0, User_Name0, ProductName resident Users

where not exists (ResourceID,ResourceID_1);

Join temp tables

Thu, 10 May 2012 07:03:08 GMT

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