Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)))
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))
Try count( {< error_code = {"*"}, deactivation_date -= {"*"} >} distinct meter_id)
I get the same result as if I remove the deactivation_date, and only get - count(if(error_code=2,distinct meter_id))
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))
Hi Daniel,
Try this one out
= count ({<error_code={'2'},deactivation_date={"IsNull(deactivation_date)<0"}>}meter_id)
count(distinct if(wildmatch(error_code,’*hardware*’,2)and len(Trim(deactivation_date))=0,meter_id))
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))
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)