Return a value according a minimun and maximun not using IF's in the expression

[#1]

hi everyone, the subject can be a bit confusing so let me explain myself better, Eg:

First: the topic is banking. I need to analize the risk that represents a product according its frecuency and amount. In order to get that information, it's necesary to use a table of transactions with the following fields:

cod_transaction

cod_client

cod_zone

cod_product

cod_channel

cod_type

date

amount

The idea it's to use the transaction to count (frecuency) how many times a product was used and to add up the amount of each transaction.

Second: having the frecuency and total amount, what i need is to locate that number in a table of scales:

cod_product	type_scale	type_description	description	minimum	maximun	risk
1	1	amount	Menos 1000 bs	0	1000	1
1	1	amount	1.000 a 25.000 bs	1001	25000	2
1	1	amount	25.001 a 50.000 bs	25001	50000	2
1	1	amount	50.001 a 200.000 bs	50001	200000	3
1	1	amount	Mas de 200.001 bs	200001	0	3
1	2	frecuency	0 a 10 veces	0	10	3
1	2	frecuency	11 a 20 veces	11	20	3
1	2	frecuency	21 a 30 veces	21	30	2
1	2	frecuency	30 a 50 veces	31	50	2
1	2	frecuency	Mas de 50 veces	50	0	1

For example, if the total amount returned was 30.000, it could be said that the actual product has a risk of 2.

My issue is HOW, in the expression, can i compare for each row of amount if the one returned is between the minimum and the maximun, if not, go the the following row (second one) and do the comparision as well until the last one of the amount scale. Of course, i'll have to do it for the frecuency too.

At the beggining, and for test purpose, i did the comparision in the expression itself, but the values of risks must be in a table (as the one showed) because the final user can change them anytime.

I hope i've explained well, if not, let me know. Thxs in advance

[Miguel_Angel_Baeyens]

Hello Leonor,

Since your values must be in a table, you can do an intervalmatch function to get, on every reload, the risk factor for each line.

You may check some sample code here.

Is this what you are looking for?

[johnw]

Intervalmatch seems like the right approach as long as it's OK for these risk levels to be fixed for that load. If you want the risk levels to respond to selections made, you could generate the IF statement in the script from the table. I can come up with an example if you need one, but you may be fine with intervalmatch and fixed risk levels.

[#4]

Hi Miguel and John, i didnt respond before 'cause i wanted to try well what u recommend me.I did, and it's indeed what i needed to solve the intervals problem with the transacction table.

Even though, it came up to me a few questions trying to implement the named function with another table, and according to what i've tried, the intervalmatch function isn't that flexible; maybe the example with the IF statement in the script coudl help me to sort it out. Not sure.

First: I explained before the use of a transacction table to get the total o transacctions and the total amount, and was those figures the ones i wantd to place into the intervals. Now, there's a client table with the average amount and number of transacctions they presume to move monthly, and are these figures i want to match in an interval in order to get the risk.

I've done it with the same procedure of Miguel's example and the one in the Help Guide. But I still have a question: How can i map to the client's table the value of the risk? What i mean is that IntervalMactch returns me a table with the field i what to place and the interval where it correspond, but i need too the risk relationed with that interval.

If i select the risk in a list object, it returns all the figures in that range, but i dont know how to relate the clients table with that risk. I hope u understand what i mean :s

Anyway, to explain myself better:

cliente_natural: it's the client table, where the field promedio_transacciones it's the average of transacctions that client presume to do, and as u can see, it's the field i want to place in the interval of scale, it's here in the scale table where's the field RISK, the one i need to map or link directly to the client.

Why? Because what i want to achive it's this table:

Look at the last column, that if i use the field with the risk: scale.riesgo, it returns 0 because there's no relation between that table and the client one (that's the dimension)

Well, thxs so so mucho in advance! i really hope u can help me (as u always do).

Regards.

1. Is it possible to manage in the same scale table the intervals for amount and frecuency?? using a type field to identify wich one it's to be used??

[johnw]

leonor.ferrebuz wrote:Look at the last column, that if i use the field with the risk: scale.riesgo, it returns 0 because there's no relation between that table and the client one (that's the dimension)

There IS a rather direct relationship between the scale.riesgo and the cliente. A cliente gives you the cliente_natural.promedio_transacciones. That gives you a limite_superior and a limite_inferior. And that gives you a scale.riesgo. Since sum(scale.riesgo) for the clientes is 0, I would guess that something went wrong with the intervalmatch itself. I've attached an example with fields, tables and data that I think matches what you're trying to do. The intervalmatch produces what I'd consider the correct values for sum(scale.riesgo).

leonor.ferrebuz wrote: Is it possible to manage in the same scale table the intervals for amount and frecuency?? using a type field to identify wich one it's to be used??

Probably, but I wasn't able to work it out quickly, and I can't work on it any more today, so I'll have to get back to that one.

[johnw]

First, I apologize for any incorrect language. Second, I'm not sure if this is exactly what you asked for. I do manage intervals for both amount and frequency in the same scale table. But rather than leave the table in place, I do a bunch of left joining to put everything in one table at the end. I think that's cleaner than leaving the tables separate.

[#7]

John, I appreciate your response. Your lastest post have helped me a lot.

Certainly the field scale.riesgo IT IS relationed with the client. The problem was layer 8 (user= me) because instead of using numbers for risk i used text, and of course when i tried to sum that field, it returns 0.

Thxs, regards.