Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I am having data based on location IDs .
I have multiple location ids and there are two location ids having same poldimid with different values.
I need to show data based on Range selection like bucket
=<5m
>5M to <=10M
>10M to <=15 M
Location id123
Value 1: 5000000
Value 2 :12 M
Location id 123 is having data for =<5 M count is 1
Then >10 M Count is 1
The other location ID 456 :
value 1:2M
Value 2 : 6 M
Location id 456. Will come in <=5 M range and count is 1
Second value should come in >5 M range
I am showing data in table for all location ids by default.
The count is coming and total sum values are coming and range also displaying correct.
When i select individual location ids data is coming in one range is correct and other one is dispalying same range or different range based on total value
For particual this scenario its coming same range but total value is wrong.its showing two different location sids sum in range .it snot showing individual sum
If we select these location ids once at a tine in filter data is displaying correct.
The poldim id available in these two locations and i am doing group by sic and poldimid
Values are differ for individaul location ids based in subject id which is unique in table.
When we select individual location ids the total is calculating over all sum for based on poldimid
In both selections its showing total sum for that poldim id range
But i should get individual sum
Main table:
Sic&poldimid as Key,
Pln no,
LicNo,
Poldimid,
Locationid,
Subjdid,
Sic,
Values,
Dept;
Resident:
Load
Sic&poldimid as Key,
Rowno() as Countofpols,
Sum(values) total
Resident t1
Group by
Sic,poodimid;
I must do group by onlh Soc and poldimid
Resulsts:
All locationids values apperaing correct in range
For individual for locationid 123
<=5M. 5000000
>5M to <=10M. 0
>10M to <=15 M. 14 M
but actual values is 12 M AND 2M Is from location 456 value since it has same poldim id
It is showing total sum value based on poldimid
But i need to get only 12 M value in thta range
If we select 456 location its showing 14 M value for
But actual value should dispay 2 M under <=5 M range
It sounds like you are having an issue with the group by statement in your Resident table.
It appears that the group by statement is only grouping by Sic and Poldimid, which is causing the values from different location ids with the same Poldimid to be combined in the same range.
To fix this, you can add the Locationid field to the group by statement in your Resident table. This will ensure that the values for each Locationid are grouped separately, and the ranges will be calculated correctly for each individual Locationid.
Here's an example of how you can modify the Resident table:
Resident: Load Sic&poldimid as Key, Rowno() as Countofpols, Sum(values) total Resident t1 Group by Sic,poodimid, Locationid;
With this modification, the Resident table will group the data by Sic, Poldimid, and Locationid, which will ensure that the values for each Locationid are calculated separately, and the ranges will be correct for each individual Locationid.
It could be that you need to do a join on the table with your main table to get the correct values, or maybe you need to do a sub-set of the data, that you want to see in the table.
Please note that this is just an example, you might want to adjust the formula according to your data and needs.