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

[Masond3]

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

[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. 

1. Add a RecordID to the dataset.
2. 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
3. use CharToInt([Values]) to get the ascii code for each character.  
4. 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 ;

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

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 "-"

[danilang]

Try this syntax

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

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