topic Re: Not exists in QlikView

Not exists

sivarajs — Tue, 02 Apr 2013 13:59:18 GMT

Hi,

I have the scenario like below

Tab1:

load Username,Code

from a.qvd;

Tab2:

load Username,Code

from b.qvd;

I need to find out what are all the code in Tab1 is not present in Tab2 based on user name

example: If user has 10 codes in Tab1 but 6 in Tab2

i need to find out whats that 4 left

Regards,

Sivaraj

Re: Not exists

Tue, 02 Apr 2013 14:02:28 GMT

Hi!

Try o Left outer Join!

By Rebeca

Re: Not exists

jagan — Tue, 02 Apr 2013 14:08:36 GMT

Hi,

Left Join both the tables

Tab1Temp:

load Username,Code

from a.qvd;

Left Join (Tab1Temp)

load Username,Code,

Code AS TempCode

from b.qvd;

Tab1:

LOAD

Username,

Code,

If(TempCode= Code, 1, 0) AS IsCodeExistsInTab2

RESIDENT Tab1Temp;

Drop Table Tab1Temp;

In the above script use IsCodeExistsInTab2 flag to find the missing and exists codes for a user.

Hope this helps you.

Regards,

Jagan.

Re: Not exists

Wed, 03 Apr 2013 05:30:36 GMT

You can get the Usernames like the below:

Load Tab2 first and then tab1

Tab2:

load Username,Code

from b.qvd;

Excluded_UserNames_Tab1:

load Username as Username1,

Code as Code1

from a.qvd

where not exists (Username,Username);

Here the field Username1 will contain the records which are present in Tab1 but not in Tab2.

Actually these are the features in Qlikview (like the exists function) which will help you to reduce your code than what have been in sql.

Re: Not exists

er_mohit — Wed, 03 Apr 2013 07:41:02 GMT

hi try this

LOAD * Inline [

Username, Code

1, a

2, b

3, c

4, d

5, e

6, f

];

Right Join

LOAD * INLINE [

Username, Code

1, a

2, b

3, c

4, d

5, e

6, f

7, g

8, h

9, i

10, j

11, k

]where not exists(Username)

;