topic Re: Else Statment not returning in column formula in Discussions

Else Statement not returning in column formula

danielrbiber — Fri, 09 Jun 2023 19:45:38 GMT

I'm creating a new column with some regex statements. I have the tough regex bit figured out but the else statement returns <no data>? Any ideas as to why this is? I have several other if containing column formula of the same syntax that return the else statements.

The formula should return the integer directly before " ct" or "ct" or should return "bulk".

For example:

:Packaging :Packaging_Ct

abso 400ct 400

ebso 100 ct 100

fiber bulk

Data Table("MetaDataGelSplit") << New Column(
	"Packaging_Ct",
	Character,
	Nominal,
	formula(
		If(
			Num( Regex( :Packaging, "\d+(?=\s+ct)" ) ) > 0, Regex( :Packaging, "\d+(?=\s+ct)" ),
			Num( Regex( :Packaging, "\d+(?=ct)" ) ) > 0, Regex( :Packaging, "\d+(?=ct)" ),
			"bulk"
		)
	)
);

Thanks in advance for the help.

Re: Else Statment not returning in column formula

txnelson — Wed, 28 Apr 2021 17:13:23 GMT

If a numeric value is not found in the Regex it does not return a zero, it returns a missing value, by changing the comparisons to using IsMissing() the logic works correctly

current data table() << New Column(
	"Packaging_Ct",
	Character,
	Nominal,
	formula(
		If(
			isMissing(Num( Regex( :Packaging, "\d+(?=\s+ct)" ) )) ==0, Regex( :Packaging, "\d+(?=\s+ct)" ),
			isMissing(Num( Regex( :Packaging, "\d+(?=ct)" ) )) == 0, Regex( :Packaging, "\d+(?=ct)" ),
			"bulk";
		);
	)
);

Re: Else Statmente not returning in column formula

Mark_Bailey — Wed, 28 Apr 2021 17:45:52 GMT

In addition to @txnelson's advice, I try to build up the formula by starting with the basic Regex() function. That is, before applying Num(), make sure that you are getting what you need from Regex() first. Don't add unnecessary layers to the formula until you are satisfied that the lowest level is working for you.

Also, Regex() is relatively 'expensive,' so I would save the result from the first call in a variable and re-use it instead of call Regex() again with the same expression. it might make a big difference in large data tables.

Re: Else Statmente not returning in column formula

vince_faller — Wed, 28 Apr 2021 17:59:21 GMT

Seems like you could cut out one of the regex entirely as well.

n = Regex( :Packaging, "(\d+)(?=\s*ct)", "\1");
if(ismissing(num(n)), 
	"bulk", 
	num(n)
)