Dear community,
I have one complex formula as below and would like to ask what this formula plan to do:
Formula One
IIF((REGEX_Match([NUMBER], "(\d{12})")=-1
AND TONUMBER(Substring([NUMBER],2,2))<13
AND TONUMBER(Substring([NUMBER],4,2))<32)=-1,1,0)
Formula Two
IIF((([DATETIME1] > "2021-05-04 23:59:59" AND [DATETIME1] < "2021-10-01 00:00:00"))=-1,1,0)
Could someone explain what is the function of this formula as i plan to edit the formula after understanding it.
Many thanks for the help
Solved! Go to Solution.
Formula 1 is probably checking if something is a date:
First formula has 3 components:
1) Is [number] 12 digits.
2) is the two digit substring at starting at number[2] < 13 (this is probably realating to a month ie 01-12)
3) is the two digits subsstring starting at number[4] <32 (ie 31 or lower).
If these are all true the value will be 1. else it will be 0
Formula 2 checks if a date is between 2021/5/5 and 2021/9/30 inclusive. if it is it gets a value of 1 - otherwise it's a 0.
@SH_94
Just want to add to comment of @apathetichell .
Your "NUMBER" column is a 12 digit nu
IIF is a conditional statement defined as below.
IIF
IIF(bool, x, y): Returns x if bool is true, else returns y.
In your Formula One
Bool equals "(REGEX_Match([NUMBER], "(\d{12})")=-1 AND TONUMBER(Substring([NUMBER],2,2))<13 AND TONUMBER(Substring([NUMBER],4,2))<32) =-1"
X equals 1
Y equals 0.
So intention here is when all 3 conditions are True, it return 1, else 0.
But there is a catch here. according to the help of alteryx, the true is not always "-1".
Therefore I would modify your Formula One as below and same applies to Formula Two to be safe.
In your case, there are identical though.
IIF((REGEX_Match([NUMBER], "(\d{12})")
AND TONUMBER(Substring([NUMBER],2,2))<13
AND TONUMBER(Substring([NUMBER],4,2))<32),1,0)