Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
NEW webinar Dec. 7th: 2023 Outlook, A Pivotal Year for Data Integration SIGN ME UP!
cancel
Showing results for 
Search instead for 
Did you mean: 
ShirSandoval
Creator
Creator

IF ANIDADOS

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'))

ShirSandoval_0-1630448402497.png

 

 

 

Labels (1)
1 Solution

Accepted Solutions
anthonyj
Creator III
Creator III

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

View solution in original post

4 Replies
anthonyj
Creator III
Creator III

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
Creator
Creator
Author

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
Creator III
Creator III

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
Creator
Creator
Author

Mil gracias Anthony, funciono.