selecting a value in a table of intervals

[PabloOrtiz]

Hi all, I have the following situation:

A table of intervals:

Servicio	ImporteDesde	ImporteHasta	Porcentaje
S1	0	5000	2
S1	5001	15000	3
S1	15001	999999	4
S2	0	10000	2
S2	10001	20000	3
S2	20001	999999	4

On the other hand I have a service table with some amounts:

Servicio	CosteServicio
S1	6.000,00 €
S2	22.000,00 €

I need to get the following result table:

CodServicio	CosteServicio	Porcentaje
S1	6.000,00 €	3
S2	22.000,00 €	4

Thanks in advance.

[swuehl]

Have a look at the INTERVALMATCH LOAD prefix in the extended version.

IntervalMatch

[sunny_talwar]

Try this:

Table:

LOAD Servicio,

    ImporteDesde,

    ImporteHasta,

    Porcentaje

FROM

[https://community.qlik.com/thread/212217]

(html, codepage is 1252, embedded labels, table is @1);

Table2:

LOAD * Inline [

Servicio, CosteServicio

S1, 6000

S2, 22000

];

Left Join (Table2)

IntervalMatch (CosteServicio, Servicio)

LOAD ImporteDesde,

  ImporteHasta,

  Servicio

Resident Table;

Left Join (Table2)

LOAD *

Resident Table;

DROP Table Table;

[PabloOrtiz]

Thanks, i know the intervalmatch function, but the problem is that the values of the CosteServicio are dinamic, it depends of the users selections. I need a dinamic match between CosteServicio and Porcentaje.

[sunny_talwar]

How does user enter those values? Can you share a sample?

[swuehl]

Maybe like this in a chart with dimension Servicio

=Min( Aggr( If( CosteServicio <= ImporteHasta, Porcentaje), Servicio, ImporteHasta))

[PabloOrtiz]

Example:

Date                Servicio     CosteServicio

01/01/2016          S1                    3.000

01/01/2016          S1                    3.000

01/01/2016          S2                    11.000

01/01/2016          S2                      1.000    

01/01/2016          S2                    10.000

02/01/2016          S1                      5.000    

02/01/2016          S1                    20.000

For 01/01/2016 as a user selecction

Date                Servicio     CosteServicio

01/01/2016          S1                    6.000

01/01/2016          S2                    22.000

I need to calculate the Porcentaje for every date

[swuehl]

And how are these fields related? Maybe just add Date to the aggr() dimensions.

[sunny_talwar · Accepted solution]

Look at the attached:

[PabloOrtiz]

Thank you very much, is the solution I was looking for.