Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
agrimroquette
Contributor II

conditional count

Hi,

I want to count those values which are null from column “Role” and having interval difference(Today-Last Logon TimeStamp) more than 30 days

I have:-

In script-  IF(IsNull(Role), 'Active Account','Inactive Account') as "Active Account"

&

Formula-count(IF((Floor(Date(Today()) - Date([Last login Time], 'dd-mm-yyyy hh:mm:ss')))>29, 'DelayLogIn'))

Data looks like below-

Pre-W2KName

Role

Last Logon TimeStamp

WSPtest2

DISABLED\NO_PWDEXP

31/03/2015 17:56

WSPtest1

NO_PWDEXP

31/03/2015 17:48

WSDS

-

wpivo40

NO_PWDEXP\DELEGATE

10/08/2015 16:28

wpivo39

-

12/04/2017 12:31

wpivo38

NO_PWDEXP

07/03/2017 15:27

problem 2)

I want to count those accounts whose password never expires “NO_PWDEXP” and

   

     ROLE

DISABLED

DISABLED\NO_PWDEXP

DISABLED\NO_PWDEXP\NO_PWDREQD

NO_PWDEXP

NO_PWDEXP\ENCRYPT

NO_PWDEXP\NO_PWDREQD

NO_PWDEXP\SMART

NO_PWDEXP\DELEGATE

1 Solution

Accepted Solutions
MVP
MVP

Re: conditional count

Perhaps this:

1. =NullCount({<[Last Logon TimeStamp = {"<=$(=Date(Today() - 30))"}>} Role)

2. =Count({<ROLE = {"*NO_PWDEXP*"}>} ROLE)

The case of Role is inconsistent in your post, so please change the above to the correct casing for Role. Remember QV is case sensitive for field names, so Role is not he same field as ROLE.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
4 Replies
rahulpawarb
Valued Contributor III

Re: conditional count

Hello Agrim,

Trust that you are doing good! First at script level set the null values to a deterministic value using below statement.

Set NULLINTERPRET = -1;

Post that, perform the the date difference operation to have date related criteria in place. Also to count null values you can make use of NullCount function.

References:

https://help.qlik.com/en-US/qlikview/12.1/Subsystems/Client/Content/Scripting/CounterAggregationFunc...

https://help.qlik.com/en-US/qlikview/12.1/Subsystems/Client/Content/ChartFunctions/CounterAggregatio...

Along with this to count those accounts whose password never exprires, you can draft an expression similar to below:

Count({<Role={'DISABLED', 'DISABLED\NO_PWDEXP', 'DISABLED\NO_PWDEXP\NO_PWDREQD', 'NO_PWDEXP', 'NO_PWDEXP\ENCRYPT', 'NO_PWDEXP\NO_PWDREQD', 'NO_PWDEXP\SMART', 'NO_PWDEXP\DELEGATE'}>Pre-W2KName}

Please correct me if I misinterpreted.

Hope this will be helpful.

Regards!

Rahul

agrimroquette
Contributor II

Re: conditional count

Hi Rahul,

Thanks for quick reply,

Count({<Role={'DISABLED', 'DISABLED\NO_PWDEXP', 'DISABLED\NO_PWDEXP\NO_PWDREQD', 'NO_PWDEXP', 'NO_PWDEXP\ENCRYPT', 'NO_PWDEXP\NO_PWDREQD', 'NO_PWDEXP\SMART', 'NO_PWDEXP\DELEGATE'}>Pre-W2KName}

this expression is giving error and i want only those which have value like "NO_PWDEXP" not all of them.


2>I want to count those values which are null from column “Role” and having interval difference(Today-Last Logon TimeStamp) more than 30 days


can you give me formula from which i can get count of Pre-W2KName which are null from column “Roleand interval difference more than 30 days?

MVP
MVP

Re: conditional count

Perhaps this:

1. =NullCount({<[Last Logon TimeStamp = {"<=$(=Date(Today() - 30))"}>} Role)

2. =Count({<ROLE = {"*NO_PWDEXP*"}>} ROLE)

The case of Role is inconsistent in your post, so please change the above to the correct casing for Role. Remember QV is case sensitive for field names, so Role is not he same field as ROLE.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
rahulpawarb
Valued Contributor III

Re: conditional count

You can refer the inputs provided by jontydkpi . This will help you to get desired results.

Regards!

Rahul

Community Browser