Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi hoping for some help on this.
I have latitude and longitude coordinates to all Family Doctors Practices in our database. I have calculated the distances from our central point (hospital) to each of these practices.
How can I create a dimension so that I can 'bucket' the practices together based on distance (ie. all practices within 5km radius, 10km radius etc)
Right now... the code below ... obviously just assigns 1 value to each practice so when I choose 5km from my filter it will show me all practices within 5km. But if I choose 10km in my filter it won't show me those within 5km also.
Wondering if there is a way I write this in my script so that if I choose 10km on its own it will show me all practices within 0 to 10km etc.
Hope I'm explaining myself well.
Thanks
Ciara
If(Distance>=0 and Distance<5.1
,
'5km'
,
If(Distance>=0 and Distance<10.1
,
'10km'
,
If(Distance>=0 and Distance<20.1
,
'20km'
,
If(Distance>=0 and Distance<50.1
,
'50km'
,
'Other'
)
)
)
) as DistanceRadius
@Ciara try below which will be well performant as compared to interval match approach as it is using fieldvalue function with do while loop
Distance:
load ..,
Distances
FROM Table;
Range:
LOAD * Inline [
Row, From, To, Distance_radius
1,0,5, 5KM
2,0,10, 10KM
3,0,20, 20KM
4,0,50, 50KM
5,51,1000, Others ];
let vRowFinal = Peek('Row',-1,'Range')+1;
let vRow=1;
do UNTIL vRow = vRowFinal
let vFrom = Peek('From',$(vRow)-1,'Range');
let vTo = Peek('To',$(vRow)-1,'Range');
let vDistance_radius = Peek('Distance_radius',$(vRow)-1,'Range');
Distance_radius:
LOAD *,
'$(vDistance_radius)' as Distance_radius
where Distances >=$(vFrom) and Distances <= $(vTo);
LOAD FieldValue('Distances',RecNo()) as Distances
AutoGenerate FieldValueCount('Distances');
LET vRow = $(vRow)+1;
LOOP
DROP Table Range;
@Ciarayou can use IntervalMatch for this, like :
Data:
load * inline [
Distance
0
1
5
10
5
15
20
40
80
81
22
25
14
7
];
IntervalLog:
load * inline [
Start, End, DistanceRadius
0,5.1,5km
0,10.1,10km
0,20.1,20km
0,50.1,50km
0,999999999999999999,Other
];
Inner Join IntervalMatch ( Distance )
LOAD Start, End
Resident IntervalLog;
output:
@Ciara in 'Others' do you want all distances or distances >50?
@Ciara try below which will be well performant as compared to interval match approach as it is using fieldvalue function with do while loop
Distance:
load ..,
Distances
FROM Table;
Range:
LOAD * Inline [
Row, From, To, Distance_radius
1,0,5, 5KM
2,0,10, 10KM
3,0,20, 20KM
4,0,50, 50KM
5,51,1000, Others ];
let vRowFinal = Peek('Row',-1,'Range')+1;
let vRow=1;
do UNTIL vRow = vRowFinal
let vFrom = Peek('From',$(vRow)-1,'Range');
let vTo = Peek('To',$(vRow)-1,'Range');
let vDistance_radius = Peek('Distance_radius',$(vRow)-1,'Range');
Distance_radius:
LOAD *,
'$(vDistance_radius)' as Distance_radius
where Distances >=$(vFrom) and Distances <= $(vTo);
LOAD FieldValue('Distances',RecNo()) as Distances
AutoGenerate FieldValueCount('Distances');
LET vRow = $(vRow)+1;
LOOP
DROP Table Range;