As I suspected, there is a cartesian product going on between your "sme community" file and the shapefile.
The region value 'WILAYAH II / PALEMBANG' appears 9500 times in your shapefile and I believe you are joining on that column. So every that particular row in the "sme community" file is repeated 9500 times. You can see similar counts for other region values.
Your shapefile is much more granular than your other datafile!
If the concern is the amount getting multiplied many thousands of times (and not the row count), a possible way to work around it (and I suggest you do it with extreme care) is to change the default aggregation of your measure to average. However, this has its own pitfall and caveats.
I already test some data, if I use average or distinct value, the result is not like my expectation.
Is it another way to create a region with only 1 row in shape file (not 9500 row for only 'WILAYAH II / PALEMBANG')
1 of 1 people found this helpful
>>Is it another way to create a region with only 1 row in shapefile (not 9500 rows for only 'WILAYAH II / PALEMBANG')
Possibly, by some kind of manipulation of the shapefile in software like QGIS.
What I don't understand is that when you initially asked the question, I was under the impression that you had much granular data, but now it appears that your actual data is at a regional level?
Does your community file only have two columns in it: Region and Amount?
Where is that huge "IF" statement being used?
From the values in the IF statement, I think you have a shapefile that is way too granular. All you need is an administrative level 2 (city, district, regency - kota, kabupaten) Shapefile. It only has 522 rows compared to 70000 records in your shapefile which has village-level data. I could be wrong though. I don't know how your actual data looks.
thank you for your help. I got shapefile from my friend. yes it's too granular, I will find out to simplify the shapefiles into kabupaten/kota and province only.
again, thank you very much....