How to count the same the same value in 3 different columns

[Anonymous]

Hi All,

I have a sample table as shown below:

UserLogin	Usr_create_date	LDAP_Status	ADStatus	EmailStatus
ABC	10-Oct-2018	P	P	P
XYZ	10-OCT-2018	P	Null	P
bbb	10-Oct-18	P	P	null

Above is a sample table where last 3 columns have status of these account creation marked with 'P' if success else null.

I have a requirement to create a chart where need to display the total account created with total failed. Here I have 3 account per user and there respective status. As per the above example, I need output date wise no: of accounts created and failed. It means adding the total no of accounts created on 10th oct and total failed.

Please suggest.

Thanks in advance.

[pauljohansson]

Hej,

I would recommend you to create a table like this:

User	Date	Account	Result
ABC	2019-01-01	LDAP	Success
ABC	2019-01-01	Email	Fail

It would be a more Qlik optimized table that you easily can create graphical objects on top of,

br

Paul

[Anonymous]

Thanks Paul. This can be done but there are other charts which require data to be in above format.

[dplr-rn]

Why dont you create a counter column in the script?

If(isnull(LDP) or isnull(adstatus), 1, 0) [failure count]

or did i miss something

[amit_gupta]

Try this, This expression will work with Dimension "Usr_create_date",

It will give you total number account's created =>>

Sum(aggr(if((If(Match(LDAP_Status,'P'),1,0)+If(Match(EmailStatus,'P'),1,0)+If(Match(ADStatus,'P'),1,0))=3,1,0),UserLogin))

It will give you total number account's Failed=>>

Sum(aggr(if((If(Match(LDAP_Status,'P'),1,0)+If(Match(EmailStatus,'P'),1,0)+If(Match(ADStatus,'P'),1,0))<3,1,0),UserLogin))

I hope It will work for you