Qlik Community

Ask a Question

New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor
Contributor

how can i find data according to certain criteria?

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

1 Solution

Accepted Solutions
Specialist III
Specialist III

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!

View solution in original post

4 Replies
MVP
MVP

Try Intervalmatch().

Specialist III
Specialist III

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!

View solution in original post

Contributor
Contributor

thanks very much!!

Specialist III
Specialist III

You're wellcome