If field is blank error
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hello,
So I'm working on a formula where the dates where Null and I cleansed it to blanks but need a specific output if the days are empty. I currently have below but it is malformed. Any suggestions?
IF ToNumber([Days Between])<=30 Then 'Pass'
Elseif ([Days Between])>30 Then 'Reseach'
Elseif IsEmpty([Days Between]) Then 'N/A'
Else 'Research' ENDIF
I Used this to get my 'Days Between' If that's needed.
DateTimeDiff([Appraisal_Sent_To_Applicant_Date],[Appraisal_Received_Date],'Days')
Thanks in advance for your time.
Solved! Go to Solution.
- Labels:
- Error Message
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
In the first part of your statement you use ToNumber on Days Between, but you dont do this for the second part. I believe this will be your issue.
Either use the ToNumber function again, or put speech marks around the 30 and treat it as text 😊
Also in the formula where you have created the Days between, you will be able to select if you want the output to be treated as a string or an int etc. So perhaps setting it to int there will be your better option, and drop the ToNumber function all together
Happy Alteryxing
Happy Alteryxing
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thank you that corrected the formula but its still showing blanks as Pass. how do I handle Empty cells?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Howdy @CRogers22 feel free to give this a go and let me know what happens:
IF ToNumber([Days Between]) <= 30 THEN 'Pass'
ELSEIF ToNumber([Days Between]) > 30 THEN 'Research'
ELSEIF IsEmpty([Days Between]) THEN 'N/A'
ELSE 'Research' ENDIF
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Days Between | Automated Results |
Pass | |
Pass | |
Pass | |
Pass | |
Pass | |
Pass | |
Pass | |
Pass | |
Pass |
Still returning Pass for blank cells
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I would try swapping the IsEmpty for IsNull, but I would also question why the DateTimeDiff function was returning nothing, are you able to share an example of the data?
Happy Alteryxing
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
IF
Isempty([DaysBetween])
THEN
'N/A'
ELSEIF
ToNumber([DaysBetween]) <= 30
THEN
'Pass'
ELSE
'Research'
ENDIF
Changing the order has fixed it on my end, I guess empty is less than 30, but if we check for that first it will mark it as N/A
This was also removes the need for the >30 check as it has the same output as the else
Happy Alteryxing
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Appraisal_Received_Date | Appraisal_Sent_To_Applicant_Date | Days Between | Automated Results |
2024-05-01 | Pass | ||
2024-05-31 | Pass | ||
2024-06-03 | Pass | ||
2024-05-21 | Pass | ||
2024-05-31 | Pass | ||
2024-05-31 | Pass | ||
2024-04-16 | Pass | ||
2024-05-24 | Pass | ||
2024-05-24 | Pass |
Its returning nothing because it was Null for the missing date, with the cleanse it made those Cells blank.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
This fixed it! thank you!
