IF ANIDADOS

[ShirSandoval]

Buenas tardes comunidad,  

Estoy creando la siguiente dimensión, el primer IF funciona perfecto pero al agregar el segundo IF (lo que esta en negrita) me coloca 0 y -1 en toda la columna. 

=If (ConvenioID <>'1_Boston' and
ConvenioID <>'2_Boston' and
ConvenioID <>'3_Boston' and
ConvenioID <>'4_Boston' and
ConvenioID <>'5_Boston' and
ConvenioID <>'21_Boston' and
ConvenioID <>'22_Boston' and
ConvenioID <>'23_Boston' and
ConvenioID <>'24_Boston' and
ConvenioID <>'25_Boston' and
ConvenioID <>'26_Boston' and
ConvenioID <>'27_Boston' and
ConvenioID <>'28_Boston' and
ConvenioID <>'29_Boston' and
ConvenioID <>'30_Boston' and
ConvenioID <>'31_Boston', ConvenioID, 'Boston 15'

=If (ConvenioID <>'6_Boston' and
ConvenioID <>'7_Boston' and
ConvenioID <>'8_Boston' and
ConvenioID <>'9_Boston' and
ConvenioID <>'10_Boston' and
ConvenioID <>'11_Boston' and
ConvenioID <>'12_Boston' and
ConvenioID <>'13_Boston' and
ConvenioID <>'14_Boston' and
ConvenioID <>'15_Boston' and
ConvenioID <>'16_Boston' and
ConvenioID <>'17_Boston' and
ConvenioID <>'18_Boston' and
ConvenioID <>'19_Boston' and
ConvenioID <>'20_Boston', ConvenioID, 'Boston 30'))

[anthonyj]

Hi,

Estoy usando traductor de Google.

El problema es que una instrucción if anidada comienza en la condición falsa de la primera instrucción if. Por el momento tienes 'Boston 15' en condición falsa. Entonces, si el valor no está en la primera lista, reemplácelo con 'Boston 15'. Ese es el final de la condición. Si puede explicar lo que está tratando de lograr, creo que podemos ayudarlo con la sintaxis.

Otra forma de lograr lo que busca es usar la función ‘match’ como esta:

=if( not match(ConvenioID
,'1_Boston'
,'2_Boston'
,'3_Boston'
, '4_Boston'
, '5_Boston'
,'21_Boston'
,'22_Boston'
,'23_Boston'
,'24_Boston'
,'25_Boston'
,'26_Boston'
,'27_Boston'
,'28_Boston'
,'29_Boston'
,'30_Boston'
,'31_Boston'), ConvenioID,

If (match(ConvenioID
,'6_Boston'
,'7_Boston'
,'8_Boston'
,'9_Boston'
,'10_Boston'
,'11_Boston'
,'12_Boston'
,'13_Boston'
,'14_Boston'
,'15_Boston'
,'16_Boston'
,'17_Boston'
,'18_Boston'
,'19_Boston'
,'20_Boston'), ConvenioID, 'Boston 30'))

Anthony

[ShirSandoval]

Hello Anthony,

What I need is to create the field AGREEMENT_NAME, I have the AgreementID field, where I have all the insurance companies with the number of the day the loan was generated. So if it was generated on 08/01/2021 I have COnvenioID 01_Boston.

I need to say the following:

Boston 15 = 21_Boston to 05_Boston of each month.

Boston 30 = 06_Boston to 20_Boston

San Cristobal 05 = 16_San Cristobal to 30_San Cristobal of each month

San Cristobal 20 = 01_San Cristobal to 15_San Cristobal of each month

Attached excel. 

[anthonyj · Accepted solution]

Hi @ShirSandoval ,

It looks like the name of the company dictates the date range condition required for 'if' statement. I don't know how many companies you're dealing with but it was very helpful that you mentioned that the ConvenioID was an amalgamation of the TransDate and company. I was able to leverage this in this possible solution:

=if(match(Empresa_Seguro, 'San Cristobal', 'Mercantil Andina'),
if(DIA_PRESTAMO <= 15, '20_' & Empresa_Seguro, '05_' & Empresa_Seguro),
if(Empresa_Seguro = 'Boston',
if(DIA_PRESTAMO >=21 or DIA_PRESTAMO <= 5,
'Boston 15', 'Boston 30'),
Empresa_Seguro))

There was a company that you didn't mention in your requirements so I've added it as an example in the code using the 'Match' function.

I hope this helps but let me know if you need any clarification.

Thanks

Anthony

[ShirSandoval]

Mil gracias Anthony, funciono.