Cell Appears Blank or Empty but Formula picking it up as populated

Team,

I am a little confused on a scenario i am encountering.

I have a formula that evaluates if a cell is populated or not. However i have scenario below, where to me the "Value" field appears blank / empty but the formula is staying that its populated.

ideally as the "Value" Column is blank it should return "Fail" and not "Pass"

Formula :
if isnull([Value]) or isempty([Value]) then 'Fail 'else 'Pass' endif

Regards
Masond3

Common Use Cases

Accepted answers

All comments

DataNath

Hey @Masond3, if you wrap the field in the Trim() function to get rid of whitespace (which is what will be populating the cell, even though it appears empty), that should help:

if isempty(Trim([Value])) then 'Fail 'else 'Pass' endif

I've also removed IsNull() from your statement - IsEmpty() evaluates both empty AND null values so covers both bases!

Masond3

@DataNath Thanks for your swift response. Unfortunately that Formula didnt work, there something that i cant see via the eye but i can see whilst on notepad++.

Anyway to show those in alteryx ?

DataNath

@Masond3 what can you see in Notepad++? It may be worth doing something along the lines of:

if isempty(Trim(Replace([Value],'<character seen in Notepad++>',''))) then 'Fail 'else 'Pass' endif

Where you just paste whatever is appearing in Notepad++ into the Replace() function's target argument?

Masond3

@DataNath Potentially, but means its not scalable as always need to maintain that list

DataNath

Gotcha @Masond3 - what should be in the [Value] column? If it's supposed to be something in particular like a numeric value, string or so on then we can likely just use RegEx to check whether it matches the expected pattern, which the current blank appearance wouldn't.

Masond3

@DataNath
So In the earlier part of the flow i have the following formula ; (which is evaluating the "name" field to see if there any chars that don't meet that criteria)

REGEX_Match([name], '.*[A-Za-z0-9ÀÈÌÒÙàèìòùÁÉÍÓÚÝáéíóúýÂÊÎÔÛâêîôûÃÑÕãñõÄËÏÖÜŸäëïöüŸ¡¿çÇŒœßØøÅåÆæÞþÐð!"#$%&()*+,-./:;<=>?@[\]^_`{|}~¢£¤¥¦§¨©ª«¬®¯°±²³´µ¶·¸¹º»¼½ ¾}].*')

It puts the result into a column called "NameCharCheck"

I then have another formula that evaluates "NameCharCheck" column then puts "chars" that invalided into a column called "NameHelper"

IF [NameCharCheck] = "-1" THEN
REGEX_Replace([name], '.*[A-Za-z0-9ÀÈÌÒÙàèìòùÁÉÍÓÚÝáéíóúýÂÊÎÔÛâêîôûÃÑÕãñõÄËÏÖÜŸäëïöüŸ¡¿çÇŒœßØøÅåÆæÞþÐð!"#$%&()*+,-./:;<=>?@[\]^_`{|}~¢£¤¥¦§¨©ª«¬®¯°±²³´µ¶·¸¹º»¼½ ¾}].*', '')
ELSE "" ENDIF

I then transpose the data ( from the "Helper" Columns). from here i am trying to evaluating whether its "empty or null" (only interested in records that have a value) ( but as you can see from the previous post) its flagging records as populated but i dont see a value

danilang

Hello @Masond3

Is there any way that you can post the values from the [Values] column in a csv. That would enable us to dive into the actual offending characters.

If you can't post the data, you can try this.

Add a RecordID to the dataset.
Split the value column to rows using a Regex tools set to tokenize using "." as the regex expression. This will put every character into its own record
use CharToInt([Values]) to get the ascii code for each character.
the unique list of these codes will tell you the extra characters that you need to include in your regex filter. Use the \x0000 syntax to include any non-printable characters

Dan

Masond3

@danilang

Please two files;
1) Input data and output
2) Current Error i encounter when updating the record via the UI in the DB

The reason for this record to fail is that the "-" in the name is not permitted list of characters list

This is the list of accepted chars i have been sent ;

! " # $ % & ' ( ) * + , - . / 0 1 2 3 4 5 6 7 8 9 : ; < = > ?
@ A B C D E F G H I J K L M N O P Q R S T U V W X Y Z [ \ ] ^ _
` a b c d e f g h i j k l m n o p q r s t u v w x y z { | } ~
¡ ¢ £ ¤ ¥ ¦ § ¨ © ª « ¬ ® ¯ ° ± ² ³ ´ µ ¶ · ¸ ¹ º » ¼ ½ ¾ ¿ À
Á Â Ã Ä Å Æ Ç È É Ê Ë Ì Í Î Ï Ð Ñ Ò Ó Ô Õ Ö × Ø Ù Ú Û Ü Ý Þ ß à
á â ã ä å æ ç è é ê ë ì í î ï ð ñ ò ó ô õ ö ÷ ø ù ú û ü ý þ ÿ

This is my regex match formula ;

It puts the result into a column called "NameCharCheck"

As you can see in the output the NameCharCheck column = -1 (which i assumed failed the validation )

the Name Helper should then call out the exact value that is failing eg the "-"

Failing.PNG

Input & Output.xlsx

danilang

Try this syntax

REGEX_Replace([name], '[^A-Z|a-z|0-9|&Agrave;|&Egrave;|&Igrave;}&Ograve;|&Ugrave;|&agrave;|&egrave;|&igrave;|&ograve;|&ugrave;|&Aacute;|&Eacute;|&Iacute;|&Oacute;|&Uacute;|&Yacute;|&aacute;|&eacute;|&iacute;|&oacute;|&uacute;|&yacute;|&Acirc;|&Ecirc;|&Icirc;|&Ocirc;|&Ucirc;|&acirc;|&ecirc;|&icirc;|&ocirc;|&ucirc;|&Atilde;|&Ntilde;|&Otilde;|&atilde;|&ntilde;|&otilde;|&Auml;|&Euml;|&Iuml;|&Ouml;|&Uuml;|&Yuml;|&auml;|&euml;|&iuml;|&ouml;|&uuml;|&Yuml;|&iexcl;|&iquest;|&ccedil;|&Ccedil;|&OElig;|&oelig;|&szlig;|&Oslash;|&oslash;|&Aring;|&aring;|&AElig;|&aelig;|&THORN;|&thorn;|&ETH;|&eth;|!||"|#|$|%|&|\(|\)|\*|\+|,|\-|\.|\/|\:|;|\<|\=|\>|\?\|@|\[|\\|\]|\^|_|`|\{|\||\}|\~|&cent;|&pound;|&curren;|&yen;|&brvbar;|&sect;|&uml;|&copy;|&ordf;|&laquo;|&not;|&shy;|&reg;|&macr;|&deg;|&plusmn;|&sup2;|&sup3;|&acute;|&micro;|&para;|&middot;|&cedil;|&sup1;|&ordm;|&raquo;|&frac14;|&frac12;| |&frac34;|\}]', "")

It replaces every character that is not in the list with blank. The pipe characters denote OR so its looking for any character that is not A-Z or a-z or 0-9, etc. Your list has several regex reserved characters("\", "[", "(", etc) so these all need to be escaped with the a leading "\" character.

When applied to your input this gives "Postalis Instituto de Previdência Complementar" removing the long dash.

Dan

Masond3

@danilang That makes sense - but how do you then reverse it to highlight that the the long dash is the char that would fail the validation ?

danilang

@Masond3

Take out the Not operator "^" at the front and it will replace all the valid characters leaving only the dash [A-Z|a-z|0-9|... vs [^A-Z|a-z|0-9|...

REGEX_Replace([name], '[A-Z|a-z|0-9|&Agrave;|&Egrave;|&Igrave;}&Ograve;|&Ugrave;|&agrave;|&egrave;|&igrave;|&ograve;|&ugrave;|&Aacute;|&Eacute;|&Iacute;|&Oacute;|&Uacute;|&Yacute;|&aacute;|&eacute;|&iacute;|&oacute;|&uacute;|&yacute;|&Acirc;|&Ecirc;|&Icirc;|&Ocirc;|&Ucirc;|&acirc;|&ecirc;|&icirc;|&ocirc;|&ucirc;|&Atilde;|&Ntilde;|&Otilde;|&atilde;|&ntilde;|&otilde;|&Auml;|&Euml;|&Iuml;|&Ouml;|&Uuml;|&Yuml;|&auml;|&euml;|&iuml;|&ouml;|&uuml;|&Yuml;|&iexcl;|&iquest;|&ccedil;|&Ccedil;|&OElig;|&oelig;|&szlig;|&Oslash;|&oslash;|&Aring;|&aring;|&AElig;|&aelig;|&THORN;|&thorn;|&ETH;|&eth;|!||"|#|$|%|&|\(|\)|\*|\+|,|\-|\.|\/|\:|;|\<|\=|\>|\?\|@|\[|\\|\]|\^|_|`|\{|\||\}|\~|&cent;|&pound;|&curren;|&yen;|&brvbar;|&sect;|&uml;|&copy;|&ordf;|&laquo;|&not;|&shy;|&reg;|&macr;|&deg;|&plusmn;|&sup2;|&sup3;|&acute;|&micro;|&para;|&middot;|&cedil;|&sup1;|&ordm;|&raquo;|&frac14;|&frac12;| |&frac34;|\}]', "")

Dan

Masond3

@danilang That's very useful indeed. I have included the additional item in Bold, as i have embedded new lines.

'[A-Z|a-z|0-9|À|È|Ì}Ò|Ù|à|è|ì|ò|ù|Á|É|Í|Ó|Ú|Ý|á|é|í|ó|ú|ý|Â|Ê|Î|Ô|Û|â|ê|î|ô|û|Ã|Ñ|Õ|ã|ñ|õ|Ä|Ë|Ï|Ö|Ü|Ÿ|ä|ë|ï|ö|ü|Ÿ|¡|¿|ç|Ç|Œ|œ|ß|Ø|ø|Å|å|Æ|æ|Þ|þ|Ð|ð|!||"|#|$|%|&|$|$|\*|\+|,|\-|\.|\/|\:|;|\<|\=|\>|\?\|@|\[|\\|\]|\^|_|`|\{|\||\}|\~|¢|£|¤|¥|¦|§|¨|©|ª|«|¬||®|¯|°|±|²|³|´|µ|¶|·|¸|¹|º|»|¼|½| |¾|\}|\s+]', "")

I have created a formula for each name and address fields which puts the invalid chars into designated columns.

I then add a recordid to the dataset then transpose the data (on all the new columns) and then have another formula to evaluate if the field is empty / null. However I am seeing record identifying as “pass” but the field appears blank

Referring to your earlier post I am trying to execute on these steps. But this is where I am stuck (probably me begin stupid )

Add a RecordID to the dataset.
Split the value column to rows using a Regex tools set to tokenize using "." as the regex expression. This will put every character into its own record
use CharToInt([Values]) to get the ascii code for each character.
the unique list of these codes will tell you the extra characters that you need to include in your regex filter. Use the \x0000 syntax to include any non-printable characters

I will try and provide more scenarios tomorrow to articulate the problem , but if you run it for that one test record you should see what I mean

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703