Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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!
Try Intervalmatch().
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!
thanks very much!!
You're wellcome