List non-existing values

[sifatnabil]

Hi,

I have a table named Salesforce that has a list of user_names. The other table, called login_table, has all usernames that were logged in. I've associated the two tables via the username field, but how can I get the list of usernames existing in Salesforce but not in the login_table?

In business terms. how can I get who was not logged in?

Salesforce:
LOAD

     username,

     data

FROM

     Salesforce_db;

login_table:

LOAD

     username,

     load_time

FROM

    login_db;

[JonnyPoole]

One way would be to load in an extra username from each side as follows. 

Then you could do an expression anywhere in the UI that calculates those who haven't logged in as:

count(distinct [SalesForce User]) - count( distinct [Login User])

Salesforce:
LOAD

     username,

     username as [SalesForce User],

     data

FROM

     Salesforce_db;

login_table:

LOAD

     username,

     username as [Login User]

     load_time

FROM

    login_db;

[jagan]

Hi,

Try like this

Salesforce:
LOAD

     username,

     data

FROM

     Salesforce_db;

NotExistsinLogin:

LOAD

     username,

     1 AS NotExistsInLoginTable

FROM

    login_db

WHERE NOT Exists(username);

login_table:

LOAD

     username,

     load_time

FROM

    login_db;

Now in front if you want to get only users who not exists in Login DB just select 1 in NotExistsInLoginTable field, the username is filtered with those names.

Hope this helps you.

Regards,

Jagan.