Mapping by Zip code

[ryankrieger]

When I am trying to map by Zipcode I get the stats table to genereate but when switching to geostats it takes 4 results from the stats table and makes it 39. Seems to be grouping by geobin instead of zip

Any ideas why this is happening?

index="indexA" servco_name="store*" servtype_id="CFAIL"
| rename zip_code as Zipcode
| lookup zip_code  Zipcode OUTPUT Lat Long
| geostats latfield=Lat longfield=Long  Sum(Count)

index="IndexA" servco_name="Store*" servtype_id="CFAIL"
| rename zip_code as Zipcode
| lookup zip_code  Zipcode OUTPUT Lat Long
| Stats Sum(Count) by Zipcode

[mydog8it]

I think you might just be missing a "BY" clause...

 index="indexA" servco_name="store*" servtype_id="CFAIL"
 | rename zip_code as Zipcode
 | lookup zip_code  Zipcode OUTPUT Lat Long
 | geostats latfield=Lat longfield=Long  Sum(Count) BY Zipcode

[to4kawa]

UPDATE:

index="indexA" servco_name="store*" servtype_id="CFAIL"
 | rename zip_code as Zipcode
 | stats Sum(Count)  as Count by Zipcode
 | lookup zip_code  Zipcode OUTPUT Lat Long
 | geostats latfield=Lat longfield=Long  values(Count) as Count  values(Zipcode) as Zipcode

how about this?

[ryankrieger]

When I add that by Zipcode clause I still get more stats than events.

796 events, 344 stats when count by Zipcode but it create 1,427 map points using geostats

[to4kawa]

is that wrong?
Do you want to count by each Zipcode?

[ryankrieger]

I would like to see number of events per Zipcode
This is how it show up with the geostats most of the geobins contains multiple zips.

geobin latitude longitude Zipcode
bin_id_zl_0_y_4_x_1 20.50500 -156.95500 96701
96740

bin_id_zl_0_y_5_x_1 35.54629 -108.64629 38637
50322

50613

[to4kawa]

Do you check Zipcode lat and lon are right?

[cdhippen]

Is this two searches or one? I also see that you're doing a sum of a count? Could you give some sample data and your desired output?

[ryankrieger]

this is 2 searches The first one gives way extra results when mapping, the 2nd one gives the correct rollup.

I can't give the raw data due to privacy but in the data there is a field called zip_code, I use a lookup to get the lat and long associated with that zip and then want to map the events by zip.