Not exists

[sivarajs]

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

[#2]

Hi!

Try o Left outer Join!

By Rebeca

[jagan]

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.

[#4]

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.

[er_mohit]

hi try this

b:

LOAD * Inline [

Username, Code

    1, a

    2, b

    3, c

    4, d

    5, e

    6, f

];

Right Join

a:

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)

;