topic Re: Exception expression in QlikView

Exception expression

Mon, 07 Apr 2014 16:38:50 GMT

Using the attached sample data, how can I find out which usernames have WorkingDayDisconnects as 'true' for ALL sessions and also for which the DisconnectionReason reason is only ever blank or 'CLOSED_BY_CLIENT'

i.e. if WorkingDayDisconnect is 'true' even once for a username, it needs to be excluded from the results. Similarly, if the DisconnectionReason is anything other than blank or 'CLOSED_BY_CLIENT' for all session, then it too must be excluded.

Re: Exception expression

Mon, 07 Apr 2014 16:49:00 GMT

How do you want the results to display?

The below expression can be added to a straight table chart with the dimension of [UserName] to detect who has 1 or more trues:

=IF(COUNT({1<[WorkingDayDisconnect] = {'true'}>} [WorkingDayDisconnect] > 0,'At least one true','All false')

If you don't want the UserName's with "trues" to show up at all you can use the following:

=IF(COUNT({1<[WorkingDayDisconnect] = {'true'}>} [WorkingDayDisconnect] = 0,'All false')

The expression would be similar for the disconnection reason.

Re: Exception expression

swuehl — Mon, 07 Apr 2014 16:49:39 GMT

Try using exists() function in where conditions, maybe like

LOAD DISTINCT

UserName as ExcludeUsername

FROM

[sample disconnects.xlsx]

(ooxml, embedded labels, table is Sheet1)

where WorkingDayDisconnect = 'true' or (len(trim(DisconnectionReason)) and DisconnectionReason <> 'CLOSED_BY_CLIENT');

LOAD UserName,

SessionStartedDate,

WorkingDayDisconnect,

DisconnectionReason

FROM

[sample disconnects.xlsx]

(ooxml, embedded labels, table is Sheet1)

where not exists(ExcludeUsername, UserName);

Re: Exception expression

Mon, 07 Apr 2014 16:51:18 GMT

just a simple list of the usernames which meet that criteria. The usernames which don't would be excluded from the results

Re: Exception expression

Mon, 07 Apr 2014 16:56:46 GMT

This doesn't remove the duplicates though. I need unique values displayed

Re: Exception expression

Mon, 07 Apr 2014 16:58:00 GMT

What other dimensions do you have in your table?

Re: Exception expression

Tue, 08 Apr 2014 07:23:42 GMT

There are no other dimensions. I need a simple list of usernames. That's it