topic Re: List non-existing values in QlikView

List non-existing values

sifatnabil — Tue, 28 Oct 2014 00:51:39 GMT

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;

Re: List non-existing values

JonnyPoole — Tue, 28 Oct 2014 02:59:22 GMT

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;

Re: List non-existing values

jagan — Tue, 28 Oct 2014 03:00:36 GMT

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.