Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Exception expression

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.

6 Replies
Not applicable

Re: Exception expression

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.

MVP
MVP

Re: Exception expression

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);

Not applicable

Re: Exception expression

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

Not applicable

Re: Exception expression

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

Not applicable

Re: Exception expression

What other dimensions do you have in your table?

Not applicable

Re: Exception expression

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

Community Browser