Creating dimension to choose distance radius

[Ciara]

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

[Taoufiq_Zarra]

@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:

[Kushal_Chawda]

@Ciara  in 'Others' do you want all distances or distances >50?

[Kushal_Chawda · Accepted solution]

@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;