Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Look at the attached:
Have a look at the INTERVALMATCH LOAD prefix in the extended version.
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;
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.
How does user enter those values? Can you share a sample?
Maybe like this in a chart with dimension Servicio
=Min( Aggr( If( CosteServicio <= ImporteHasta, Porcentaje), Servicio, ImporteHasta))
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
And how are these fields related? Maybe just add Date to the aggr() dimensions.
Look at the attached:
Thank you very much, is the solution I was looking for.