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

[varmekontrol]

Hi

So basically I have this data

meter_id	error_code	deactivation_date
123456	2
654654	2	04-05-2017
656565
2131231	Hardware_error
2333333
4444444	Componet_error	04-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)))

[Gysbert_Wassenaar]

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

[varmekontrol]

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

[sasiparupudi1]

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]

Hi Daniel,

Try this one out

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

[sasiparupudi1]

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

[Anonymous · Accepted solution]

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

[sunny_talwar]

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)