how can i find data according to certain criteria?

[ale_nilo84]

hello everyone, i have 3 tables:

Table 1: Frequency, with 3 fields: Inicio, Fin, Clase

Table 2: Distance: with 3 fields: Inicio, Fin, Clase

Table 3: Services Data: with 5 fields: Service Name, Direction,Period, Frequency and Distance.

My intention is to add to table 3 the Clase Field according to the range between Inicio and Fin of table 1 and table 3.

I add an excel doc with an example of what i intend to do.

I hope someone could helpme,

Thank you in advace

[tresesco]

Try Intervalmatch().

[Anonymous · Accepted solution]

Hi Alexis,

Try this code

Frecuencia:
LOAD
    Num(Num#(inicio,'0,00',',','.'),'#.##0,00') as Inicio_frecuencia,
    Num(Num#(fin,'0,00',',','.'),'#.##0,00') as Fin_frecuencia,
    Num(Num#(Clase,'0,00',',','.'),'#.##0,00') as Clase_frecuencia;
LOAD * INLINE [
    inicio, fin, Clase
    "0,22", "2,72", "1,5"
    "2,73", "5,23", "4,0"
    "5,24", "7,74", "6,5"
    "7,75", "10,25", "9,0"
    "10,26", "12,76", "11,5"
    "12,77", "15,27", "14,0"
    "15,28", "17,78", "16,5"
    "17,79", "20,29", "19,0"
    "20,30", "22,80", "21,6"
    "22,81", "25,31", "24,1"
];

Distancia:
LOAD
    Num(Num#(inicio,'0,00',',','.'),'#.##0,00') as Inicio_distancia,
    Num(Num#(fin,'0,00',',','.'),'#.##0,00') as Fin_distancia,
    Num(Num#(Clase,'0,00',',','.'),'#.##0,00') as Clase_distancia;
LOAD * INLINE [
    inicio, fin, Clase
    "3,46", "8,86", "6,2"
    "8,87", "14,27", "11,6"
    "14,28", "19,68", "17,0"
    "19,69", "25,09", "22,4"
    "25,10", "30,50", "27,8"
    "30,51", "35,91", "33,2"
    "35,92", "41,32", "38,6"
    "41,33", "46,73", "44,0"
    "46,74", "52,14", "49,4"
    "52,15", "57,55", "54,9"
];


Final:
LOAD 
    [Código TS],
    Sentido,
    Periodo,
    Num(Num#(frecuencia,'0,00',',','.'),'#.##0,00') as frecuencia,
    Num(Num#(distancia,'0,00',',','.'),'#.##0,00') as distancia;
LOAD * INLINE [
    Código TS, Sentido, Periodo, frecuencia, distancia
    101, Ida, p1, 3, "25,26"
    101, Ret, p1, 3, "24,05"
    101c, Ida, p1, , "12,84"
    101c, Ret, p1, , "10,69"
    102, Ida, p1, 3, "25,83"
    102, Ret, p1, 3, "24,77"
    103, Ida, p1, 3, "13,39"
    103, Ret, p1, 2, "14,93"
];

IntervalMatchFrecuencia:
IntervalMatch (frecuencia)
Load distinct Inicio_frecuencia, Fin_frecuencia resident Frecuencia;
Left Join (IntervalMatchFrecuencia)
LOAD Inicio_frecuencia, Fin_frecuencia, Clase_frecuencia RESIDENT Frecuencia;

LEFT JOIN (Final)
LOAD
    frecuencia,
    Clase_frecuencia as Clase_frecuencia_Final
RESIDENT IntervalMatchFrecuencia;

DROP TABLE IntervalMatchFrecuencia;

IntervalMatchDistancia:
IntervalMatch (distancia)
Load distinct Inicio_distancia, Fin_distancia resident Distancia;
Left Join (IntervalMatchDistancia)
LOAD Inicio_distancia, Fin_distancia, Clase_distancia RESIDENT Distancia;

LEFT JOIN (Final)
LOAD
    distancia,
    Clase_distancia as Clase_Distancia_Final
RESIDENT IntervalMatchDistancia;

DROP TABLE IntervalMatchDistancia;

This is the output:

Regards!

[ale_nilo84]

thanks very much!!

[Anonymous]

You're wellcome