Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

varmekontrol
New Contributor II

Count if "Date is null" and Value=string/number

Hi

So basically I have this data

meter_iderror_codedeactivation_date

123456

2
654654204-05-2017
656565
2131231Hardware_error
2333333
4444444Componet_error04-05-2017

What I need to Count is an error_code=number

count(distinct(if(error_code=2, deactivation_date=BLANK)))

I only want to Count the Distinct meter_id, if the error_code=2, and the Distinct meter_id, has no deactivation_date

What I need to Count is an error_code=string

count(distinct(if(error_code='*Hardw*', deactivation_date=BLANK)))

1 Solution

Accepted Solutions
arunaerra
Contributor

Re: Count if "Date is null" and Value=string/number

Hi,

Try this one

If(error_code=2 and  len(trim(deactivation_date))= 0,Count( distinct meter_id) ,if(wildmatch(error_code,'*error') and  len(trim(deactivation_date))=0, Count( distinct meter_id),0))

Capture 9.PNG

7 Replies

Re: Count if "Date is null" and Value=string/number

Try count( {< error_code = {"*"}, deactivation_date -= {"*"} >} distinct meter_id)


talk is cheap, supply exceeds demand
varmekontrol
New Contributor II

Re: Count if "Date is null" and Value=string/number

I get the same result as if I remove the deactivation_date, and only get - count(if(error_code=2,distinct meter_id))

sasiparupudi1
Honored Contributor III

Re: Count if "Date is null" and Value=string/number

count(distinct if(error_code=2 and len(Trim(deactivation_date))=0,meter_id))


count(distinct if(wildmatch(error_code,’*hardware*’)and len(Trim(deactivation_date))=0,meter_id))

baarathi
Contributor II

Re: Count if "Date is null" and Value=string/number

Hi Daniel,

Try this one out

= count ({<error_code={'2'},deactivation_date={"IsNull(deactivation_date)<0"}>}meter_id)

sasiparupudi1
Honored Contributor III

Re: Count if "Date is null" and Value=string/number

count(distinct if(wildmatch(error_code,’*hardware*’,2)and len(Trim(deactivation_date))=0,meter_id))

arunaerra
Contributor

Re: Count if "Date is null" and Value=string/number

Hi,

Try this one

If(error_code=2 and  len(trim(deactivation_date))= 0,Count( distinct meter_id) ,if(wildmatch(error_code,'*error') and  len(trim(deactivation_date))=0, Count( distinct meter_id),0))

Capture 9.PNG

MVP
MVP

Re: Count if "Date is null" and Value=string/number

May be create a flag in the script

If(Len(Trim(error_code)) > 0 and Len(Trim(deactivation_date)) = 0, 1, 0) as Flag1,

If(error_code = 2 and Len(Trim(deactivation_date)) = 0, 1, 0) as Flag2,

Count(DISTINCT {<Flag1 = {1}>} meter_id) -> This will equal 2 (123456 and 2131231)

Count(DISTINCT {<Flag2 = {1}>} meter_id) -> This will equal 1 (123456)

Community Browser