Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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?
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.
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??
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)
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??
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.
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.