Location details

[Raju_6952]

Hi,

I have data related to sensors and incidents,here the sensor create incidents and based on that sensor we are extracting location details like city, facility.now problem is that some sensors were being deleted by which the incidents created from that sensors becomes null as the location details for that sensors were not available as the base sensor has been deleted. Is there any way to show that incidents as "others" location in addition with the main available location data.

Sample data:

City incident count

Bangalore,150

Chennai,200

-,100( this count because of sensor deleted as we don't have location details).

I need to show that as "Others -100"

Thanks in advance ,

[Gabbar]

Use Coalesce:-
Coalesce(City incident,'Others') as "City Incident". 

It will change every null value in table to 'Others'.
Also please check if the value is null, blank o '-' before using coalesce.
If it isnt null please convert it to null.

[anat]

if([City incident]='-' or len(trim([City incident]))=0 ,'Others',[City incident]) as [City incident]

[Raju_6952]

Hi,

its not working beacuse city filed dont have nulls,if I add city and Incidnet count as measure, then its generating null value for some count, in that case i need to show that as "others".

Thanks.

[Or]

In your original post, you describe City as being null. In your second post, you say is does not have nulls. It may be helpful to better explain your situation and provide an example that matches in order to get answers that will help you solve the issue.

[Raju_6952]

City | IncidentCount
-------------|--------------
New York | 150
Los Angeles | 120
Chicago | 90
Houston | 80
Miami | 60

-| 100.

This is the output when I add city as dimension and count (distinct IncidentID) as measure.

[Or]

There are two suggestions above on how to deal with the nulls in City. It does not appear you've tried them?

[Raju_6952]

Hi @Or ,

I tried them but no use. 

Thanks,

[Or]

Using your exact data, and one of the suggestions above, I seem to be getting the desired result...

Load if([City incident]='-' or len(trim([City incident]))=0 ,'Others',[City incident]) as [City incident], IncidentCount INLINE [
City incident, IncidentCount
New York , 150
Los Angeles , 120
Chicago , 90
Houston , 80
Miami , 60
, 100];

[Raju_6952]

Hi @Or ,

Thanks for the update,

Will check on my end.