Help Diagnosing a Nested IF() Formula
Answered
If someone could help me diagnosis why the nested IF() formula below isn’t returning the expected result, I’d be grateful.
I’ve got a date field (LATEST_PICKUP_DATE) wherein ‘null’ dates default to a date of “1/1/0001 12:00:00,” and that date has a Datameer numerical value of -62135575764000 when used in the INT() function in the calculated field “TEST.”) When this is the case, I want to look at another date field (CREATE_DATE) and add either 6 hours or 24 hours to create a faux LATEST_PICKUP_DATE value. I want to add 6 hours when the SHIPPER is “9670” AND the DEST_FAC_NBR is “9598” AND TEST is -62135575764000, and in ALL other cases when TEST is -62135575764000, I want to add 24 hours. The formula works insofar as it computes as a valid formula, but it allows several values of “1/1/0001 12:00:00” to get through without using the CREATE_DATE and adding the appropriate number of hours.
IF(AND(#Sheet2!TEST==-62135575764000;#Sheet2!SHIPPER==9670;#Sheet2!DEST_FAC_NBR==9598);#Sheet2!CREATE_DATE_Mstr_LOCSSHIPMENT+6h;IF(AND(#Sheet2!TEST==-62135575764000;#Sheet2!SHIPPER!=9670;#Sheet2!DEST_FAC_NBR!=9598);#Sheet2!CREATE_DATE_Mstr_LOCSSHIPMENT+24h;ASDATE(CONCAT(FORMATDATE(#Sheet2!LATEST_PICKUP_DATE;"MMM dd, Y");" ";#Sheet2!LATEST_PICKUP_TIME);"MMM dd, Y HH:mm:ss")))
In English, what I thought I was saying with the formula above:
IF “TEST” equals “-62135575764000” AND “SHIPPER” equals 9670 AND DEST_FAC_NBR equals 9598, then add 6 hours to the CREATE_DATE, IF “TEST” equals “-62135575764000” AND “SHIPPER” does NOT equal 9670 AND DEST_FAC_NBR does NOT equal 9598, then add 24 hours, else concatenate LATEST_PICKUP_DATE and LATEST_PICKUP_TIME
I hope that's clear enough and look forward to any insight anyone can offer. Thank you!
-
Hello Mario.
If I properly understand the requirements, you have the following initial dataset with column types INT, INT, INT, DATE, STRING, and STRING respectively.
I believe the following formula returns the desired result.
IF(#TEST==-62135575764000 && #SHIPPER==9670 && #DEST_FAC_NBR==9598;#CREATE_DATE+6h;IF(#TEST==-62135575764000 && #SHIPPER!=9670 && #DEST_FAC_NBR!=9598;#CREATE_DATE+24h;ASDATE(CONCAT(#LATEST_PICKUP_DATE;" ";#LATEST_PICKUP_TIME);"yyyy-MM-dd HH:mm:ss")))
To break it down:
- If the condiction #TEST==-62135575764000 && #SHIPPER==9670 && #DEST_FAC_NBR==9598 met - add 6h to #CREATE_DATE
- If the condiction #TEST==-62135575764000 && #SHIPPER!=9670 && #DEST_FAC_NBR!=9598 met - add 24h to #CREATE_DATE
- In all other cases concat LATEST_PICKUP_DATE and LATEST_PICKUP_TIME and convert the resulting value into a date.
Please let me know if this helps.
-
Thanks for the response, Konsta!
Yes, you've got the right idea, but I got my original formula to work by simply dropping the "does not equal" logic out altogether (in bold below.) My guess (and if someone could confirm I'm not incorrect, that'd be great,) is that once the specific 9670/9598 records pass the first logic of the first IF() adding 6h to their CREATE_DATE, any other records with -62135575764000 in the TEST field are taken care of by the second IF() and have 24h added to the CREATE_DATE.
IF(AND(#Sheet2!TEST==-62135575764000;#Sheet2!SHIPPER==9670;#Sheet2!DEST_FAC_NBR==9598);#Sheet2!CREATE_DATE_Mstr_LOCSSHIPMENT+6h;IF(AND(#Sheet2!TEST==-62135575764000;#Sheet2!SHIPPER!=9670;#Sheet2!DEST_FAC_NBR!=9598);#Sheet2!CREATE_DATE_Mstr_LOCSSHIPMENT+24h;ASDATE(CONCAT(FORMATDATE(#Sheet2!LATEST_PICKUP_DATE;"MMM dd, Y");" ";#Sheet2!LATEST_PICKUP_TIME);"MMM dd, Y HH:mm:ss")))
-
Hello Mario.
What was the initial problem with this formula, did it return an error or an incorrect result? If there was an error, would you be able to provide the screenshot, please?Your formula works fine for me even with "does not equal" operations. I simply replaced the concatenation part with null to simplify the test.
IF(AND(#TEST==-62135575764000;#SHIPPER==9670;#DEST_FAC_NBR==9598);#CREATE_DATE+6h;IF(AND(#TEST==-62135575764000;#SHIPPER!=9670;#DEST_FAC_NBR!=9598);#CREATE_DATE+24h;null))
As well as it is.
IF(AND(#TEST==-62135575764000;#SHIPPER==9670;#DEST_FAC_NBR==9598);#CREATE_DATE+6h;IF(AND(#TEST==-62135575764000;#SHIPPER!=9670;#DEST_FAC_NBR!=9598);#CREATE_DATE+24h;ASDATE(CONCAT(FORMATDATE(#LATEST_PICKUP_DATE;"yyyy-MM-dd");" ";#LATEST_PICKUP_TIME);"yyyy-MM-dd HH:mm:ss")))
The logic of nested IF functions, in this case, is the following:
- Check the first condition AND(#TEST==-62135575764000;#SHIPPER==9670;#DEST_FAC_NBR==9598), if it is met add 6h to CREATE_DATE value.
- For all records that do not satisfy the first condition (ELSE part), check the second condition AND(#TEST==-62135575764000;#SHIPPER!=9670;#DEST_FAC_NBR!=9598).
- For all those records that match the second condition add 24h to CREATE_DATE value.
- In case a record doesn't match any of the mentioned conditions - build a data value from LATEST_PICKUP_DATE and LATEST_PICKUP_TIME values.
Datameer considers a certain IF condition as a complete expression. You could test every condition as a separate formula to confirm that it returns the expected result.
- AND(#TEST==-62135575764000;#SHIPPER==9670;#DEST_FAC_NBR==9598)
- AND(#TEST==-62135575764000;#SHIPPER!=9670;#DEST_FAC_NBR!=9598)
-
In my data set, I’ve got the following fields:
- SHIPPER (Integer)
- DEST_FAC_NBR (Integer)
- CREATE_DATE (Date)
- LATEST_PICKUP_DATE (Date)
- LATEST_PICKUP_TIME (String)
- TEST (Integer, calculated field)
- LATEST_PICKUP (Date, this is where my formula is)
When there's not a valid LATEST_PICKUP_DATE in the data, a fake value of "1/1/0001 12:00:00" acts as a placeholder. In the TEST field, I use the INT() function to convert the LATEST_PICKUP_DATE to an integer, and the result for each fake value is "-62135575764000."
My goal with my formula was to, in the field LATEST_PICKUP, concatenate the LATEST_PICKUP_DATE/TIME fields for the valid values, but where TEST=="-62135575764000", add 24 hours to the CREATE_DATE, except when SHIPPER=="9670" and DEST_FAC_NBR=="9598", then add only 6 hours.
With my original formula (the one in my first post,) the goal was to end up with a LATEST_PICKUP field with nothing but valid dates, but I was seeing some invalid values of "1/1/0001 12:00:00." My hunch was the "AND/!=" logic was allowing certain records to slip through because things like misuse of "AND" (versus OR) and using "!=" with multiple criteria has bitten me in the butt before. I removed that piece of it, and the results cleared up.
Please sign in to leave a comment.
Comments
6 comments