I wasn't able to open your workbook since it's a later version than what I have available.
You could simply check if the 4 left characters are '1-13' in the passport number.
IF LEFT ([PASSPORTNO],4) = '1-13'
ELSE 'Foreign National'
Hi, thanks for answering Subodh
But no, the passport is the numbers that do not have this structure
1 - 13 - xxx -xxxx
1-13 which I place is a range for example 8-888-8888 or 13-256-2563; goes from 1 to 13
in addition to that we have another coding that are the
Thanks for the support
Thank you for the support but the calculated field shows Foreigners to everyone mostly and as locals to the blank fields for more understanding of what I try to expose an image capture the yellows are the locals and the orange ones are the foreigners;
Y aun no contemplo los que tienen PE, E, AV, PI,
Anotación 2020-01-16 145620.png 58.1 KB
Are you saying any number that begins with with either 1 through 13?
is a resident? and the rest are foreign nationals?
Vea el adjunte. Diga me si es lo que necesita. Lo que hice es como hay menos exclusiones para internacionales los codifique primero. No supe que hacer con los nulos porque en mi mente todos necesitamos pasaportes.
How about this
IF ISNULL(STARTSWITH([CEDULA PACIENTE], '1-13')) THEN "LOCAL"
ELSEIF STARTSWITH([CEDULA PACIENTE], '1-13') THEN "LOCAL"
That won't work for his data set. The 1-13 is a range of numbers. So for example:
My first thought was splitting off the first character and checking to see if was a number or not. The problem with that is the passport code that ends in N. Also, he has nulls in his data.
PRO TIP: When exploring a data set, drop the pill you are looking at on the filters tab. Nulls will always be first. It's a quick way of checking for nulls.
They are local friend
So it is those who have this structure that describe more those who have P, E, PI etc.
How about the following
( REGEXP_MATCH([CEDULA PACIENTE], '^[1-13]')
INT(LEFT([CEDULA PACIENTE], 2) ) <=13)
ISNULL( [CEDULA PACIENTE])