Datecompare transform returns error when the variable is null

We’re trying to do a transform to calculate the life cycle of the identities but when we need to compare dates and the date of the identity is null (which is required for some identities) it returns an error. We’ve detected that the error must be in the declaration of the variables or inside the velocity argument.

We believe that the problem is a variable this uses dateCompare:

       "quarantineGreater6M": {
            "type": "dateCompare",
            "attributes": {
                "firstDate": {
                    "type": "firstValid",
                    "attributes": {
                        "values": [
                            {
                                "type": "dateMath",
                                "attributes": {
                                    "expression": "+6M",
                                    "roundUp": true,
                                    "input": {
                                        "attributes": {
                                            "input": {
                                                "attributes": {
                                                    "values": [
                                                        {
                                                            "attributes": {
                                                                "sourceName": "Source",
                                                                "attributeName": "endDate"
                                                            },
                                                            "type": "accountAttribute"
                                                        }
                                                    ]
                                                },
                                                "type": "firstValid"
                                            },
                                            "inputFormat": "dd/MM/yyyy",
                                            "outputFormat": "ISO8601"
                                        },
                                        "type": "dateFormat"
                                    }
                                }
                            },
                            {
                                "type": "dateMath",
                                "attributes": {
                                    "expression": "+6M",
                                    "roundUp": true,
                                    "input": {
                                        "attributes": {
                                            "input": {
                                                "attributes": {
                                                    "values": [
                                                        {
                                                            "attributes": {
                                                                "sourceName": "Source",
                                                                "attributeName": "endDate"
                                                            },
                                                            "type": "accountAttribute"
                                                        }
                                                    ]
                                                },
                                                "type": "firstValid"
                                            },
                                            "inputFormat": "dd/MM/yyyy",
                                            "outputFormat": "ISO8601"
                                        },
                                        "type": "dateFormat"
                                    }
                                }
                            },
                            "null"
                        ]
                    }
                },
                "secondDate": {
                    "type": "dateMath",
                    "attributes": {
                        "expression": "now",
                        "roundUp": true
                    }
                },
                "operator": "lte",
                "positiveCondition": "true",
                "negativeCondition": "false"
            }
        },
1 Like

Instead of null, pass a future/past date as per your requirements.

Hi @rcgalvez ,

Don’t use null in firstDate, instead pass the future/past date (vary as per your requirements)

I have provided the sample transform below, take a look at it.

"firstDate": {
    "attributes": {
        "input": {
            "attributes": {
                "values": [
                    {
                        "attributes": {
                            "sourceName": "<SOURCE NAME>",
                            "attributeName": "<ATTRIBUTE NAME"
                        },
                        "type": "accountAttribute"
                    },
                    "5000-12-30T00:00:00"
                ]
            },
            "type": "firstValid"
        },
        "inputFormat": "yyyy-MM-dd'T'HH:mm:ss",
        "outputFormat": "ISO8601"
    },
    "type": "dateFormat"
}

Thanks!!

1 Like

Thank you very much for you answer. I’ve fixed the code as you said, but now it doesn’t work. If the first date is lower than today, it returns false, instead of true. Do you know why?


 "quarantineGreater6M": {
            "type": "dateCompare",
            "attributes": {
                "firstDate": {
                    "type": "firstValid",
                    "attributes": {
                        "values": [
                            {
                                "type": "dateMath",
                                "attributes": {
                                    "expression": "+6M",
                                    "roundUp": true,
                                    "input": {
                                        "attributes": {
                                            "input": {
                                                "attributes": {
                                                    "values": [
                                                        {
                                                            "attributes": {
                                                                "sourceName": "Source",
                                                                "attributeName": "endDate"
                                                            },
                                                            "type": "accountAttribute"
                                                        },
                                                        "01/01/2050"
                                                    ]
                                                },
                                                "type": "firstValid"
                                            },
                                            "inputFormat": "dd/MM/yyyy",
                                            "outputFormat": "ISO8601"
                                        },
                                        "type": "dateFormat"
                                    }
                                }
                            },
                            {
                                "type": "dateMath",
                                "attributes": {
                                    "expression": "+6M",
                                    "roundUp": true,
                                    "input": {
                                        "attributes": {
                                            "input": {
                                                "attributes": {
                                                    "values": [
                                                        {
                                                            "attributes": {
                                                                "sourceName": "Source",
                                                                "attributeName": "endDate"
                                                            },
                                                            "type": "accountAttribute"
                                                        },
                                                        "01/01/2050"
                                                    ]
                                                },
                                                "type": "firstValid"
                                            },
                                            "inputFormat": "dd/MM/yyyy",
                                            "outputFormat": "ISO8601"
                                        },
                                        "type": "dateFormat"
                                    }
                                }
                            }
                        ]
                    }
                },
                "secondDate": {
                    "attributes": {
                        "input": {
                            "type": "dateMath",
                            "attributes": {
                                "expression": "now",
                                "roundUp": true
                            }
                        },
                        "inputFormat": "yyyy-MM-dd'T'HH:mm",
                        "outputFormat": "ISO8601"
                    },
                    "type": "dateFormat"
                },
                "operator": "lte",
                "positiveCondition": "true",
                "negativeCondition": "false"
            }
        },
"secondDate": {
    "attributes": {
        "inputFormat": "yyyy-MM-dd'T'HH:mm'Z'",
        "outputFormat": "ISO8601",
        "input": {
            "attributes": {
                "expression": "now/d"
            },
            "type": "dateMath"
        }
    },
    "type": "dateFormat"
}

replace this in the second Date and try.

Thank you so much for your help. I did it as you said but it still gives me false. We’re comparing a date from last year with today, it should be true.

 "quarantineGreater6M": {
            "type": "dateCompare",
            "attributes": {
                "firstDate": {
                    "type": "firstValid",
                    "attributes": {
                        "values": [
                            {
                                "type": "dateMath",
                                "attributes": {
                                    "expression": "+6M",
                                    "roundUp": true,
                                    "input": {
                                        "attributes": {
                                            "input": {
                                                "attributes": {
                                                    "values": [
                                                        {
                                                            "attributes": {
                                                                "sourceName": "Source",
                                                                "attributeName": "endDate"
                                                            },
                                                            "type": "accountAttribute"
                                                        },
                                                        "01/01/2050"
                                                    ]
                                                },
                                                "type": "firstValid"
                                            },
                                            "inputFormat": "dd/MM/yyyy",
                                            "outputFormat": "ISO8601"
                                        },
                                        "type": "dateFormat"
                                    }
                                }
                            },
                            {
                                "type": "dateMath",
                                "attributes": {
                                    "expression": "+6M",
                                    "roundUp": true,
                                    "input": {
                                        "attributes": {
                                            "input": {
                                                "attributes": {
                                                    "values": [
                                                        {
                                                            "attributes": {
                                                                "sourceName": "Source",
                                                                "attributeName": "endDate"
                                                            },
                                                            "type": "accountAttribute"
                                                        },
                                                        "01/01/2050"
                                                    ]
                                                },
                                                "type": "firstValid"
                                            },
                                            "inputFormat": "dd/MM/yyyy",
                                            "outputFormat": "ISO8601"
                                        },
                                        "type": "dateFormat"
                                    }
                                }
                            }
                        ]
                    }
                },
                "secondDate": {
                    "attributes": {
                        "inputFormat": "yyyy-MM-dd'T'HH:mm'Z'",
                        "outputFormat": "ISO8601",
                        "input": {
                            "attributes": {
                                "expression": "now/d"
                            },
                            "type": "dateMath"
                        }
                    },
                    "type": "dateFormat"
                },
                "operator": "lte",
                "positiveCondition": "true",
                "negativeCondition": "false"
            }
        },

I have been validating your transform individually. I have came to know that, your transform is comparing

Start_Date value: 2024-02-05T01:00Z
Present_Date value: 2024-07-11T00:00:00.000Z

That’s why it’s returning the negative condition. Resolve this error. Thanks!

{
  "type": "dateFormat",
  "name": "WD - HireDate",
  "attributes": {
    "input": {
      "attributes": {
        "expression": "+1d",
        "input": {
          "attributes": {
            "input": {
              "attributes": {
                "attributeName": "HIREDATE",
                "sourceName": "Workday"
              },
              "type": "accountAttribute"
            },
            "inputFormat": "MM/dd/yyyy",
            "outputFormat": "ISO8601"
          },
          "type": "dateFormat"
        },
        "roundUp": true
      },
      "type": "dateMath"
    },
    "inputFormat": "yyyy-MM-dd'T'HH:mm",
    "outputFormat": "EPOCH_TIME_WIN32"
  }
}

you can use this format. Take a look at the below link.

Thank you! We fixed that but it still gives out the wrong answer. We’ve created 2 variables to show us the values of the dates. The dates come out good, with the same format. But when we compare them, be it using dateCompare or using velocity, it always comes out saying that the lower date is the higher date. This the output we get from the variables: 2024-07-11T00:00Z > 2023-06-25T00:00Z

And this is how we tested it:

 "test": {
            "attributes": {
                "inputFormat": "yyyy-MM-dd'T'HH:mm'Z'",
                "outputFormat": "yyyy-MM-dd'T'HH:mm'Z'",
                "input": {
                    "attributes": {
                        "expression": "now/d"
                    },
                    "type": "dateMath"
                }
            },
            "type": "dateFormat"
        },
        "test2": {
            "attributes": {
                "input": {
                    "attributes": {
                        "values": [
                            {
                                "attributes": {
                                    "sourceName": "Source",
                                    "attributeName": "endDate"
                                },
                                "type": "accountAttribute"
                            },
                            "01/01/2050"
                        ]
                    },
                    "type": "firstValid"
                },
                "inputFormat": "dd/MM/yyyy",
                "outputFormat": "yyyy-MM-dd'T'HH:mm'Z'"
            },
            "type": "dateFormat"
        },
        "value": "$test > $test2"
    },

When we compare them with dateCompare or using “value”:#if($test > $test2)true{else}false#end" it always says that the lower one is the higher one.

Hi @rcgalvez ,

"type": "firstValid",
    "attributes": {
        "values": [
            {
                "attributes": {
                    "input": {
                        "attributes": {
                            "expression": "+1M",
                            "input": {
                                "attributes": {
                                    "input": {
                                        "attributes": {
                                            "values": [
                                                {
                                                    "attributes": {
                                                        "sourceName": "Source",
                                                        "attributeName": "Start_Date"
                                                    },
                                                    "type": "accountAttribute"
                                                },
                                                "01/01/2050"
                                            ]
                                        },
                                        "type": "firstValid"
                                    },
                                    "inputFormat": "dd/MM/yyyy",
                                    "outputFormat": "ISO8601"
                                },
                                "type": "dateFormat"
                            },
                            "roundUp": true
                        },
                        "type": "dateMath"
                    },
                    "inputFormat": "yyyy-MM-dd'T'HH:mm'Z'",
                    "outputFormat": "ISO8601"
                },
                "type": "dateFormat"
            },
            {
                "attributes": {
                    "input": {
                        "attributes": {
                            "expression": "+1M",
                            "input": {
                                "attributes": {
                                    "input": {
                                        "attributes": {
                                            "values": [
                                                {
                                                    "attributes": {
                                                        "sourceName": "Source",
                                                        "attributeName": "End_Date"
                                                    },
                                                    "type": "accountAttribute"
                                                },
                                                "01/01/2050"
                                            ]
                                        },
                                        "type": "firstValid"
                                    },
                                    "inputFormat": "dd/MM/yyyy",
                                    "outputFormat": "ISO8601"
                                },
                                "type": "dateFormat"
                            },
                            "roundUp": true
                        },
                        "type": "dateMath"
                    },
                    "inputFormat": "yyyy-MM-dd'T'HH:mm'Z'",
                    "outputFormat": "ISO8601"
                },
                "type": "dateFormat"
            }
        ]
    }

Replace the firstDate with the above template and also the second_date with the below template.

"secondDate": {
    "attributes": {
        "inputFormat": "yyyy-MM-dd'T'HH:mm'Z'",
        "outputFormat": "ISO8601",
        "input": {
            "attributes": {
                "expression": "now/d"
            },
            "type": "dateMath"
        }
    },
    "type": "dateFormat"
}

This change must work. Thanks!!

"quarantineGreater6M": {
    "type": "dateCompare",
    "attributes": {
        "firstDate": {
            "type": "firstValid",
            "attributes": {
                "values": [
                    {
                        "attributes": {
                            "input": {
                                "attributes": {
                                    "expression": "+1M",
                                    "input": {
                                        "attributes": {
                                            "input": {
                                                "attributes": {
                                                    "values": [
                                                        {
                                                            "attributes": {
                                                                "sourceName": "Source",
                                                                "attributeName": "Start_Date"
                                                            },
                                                            "type": "accountAttribute"
                                                        },
                                                        "01/01/2050"
                                                    ]
                                                },
                                                "type": "firstValid"
                                            },
                                            "inputFormat": "dd/MM/yyyy",
                                            "outputFormat": "ISO8601"
                                        },
                                        "type": "dateFormat"
                                    },
                                    "roundUp": true
                                },
                                "type": "dateMath"
                            },
                            "inputFormat": "yyyy-MM-dd'T'HH:mm'Z'",
                            "outputFormat": "ISO8601"
                        },
                        "type": "dateFormat"
                    },
                    {
                        "attributes": {
                            "input": {
                                "attributes": {
                                    "expression": "+1M",
                                    "input": {
                                        "attributes": {
                                            "input": {
                                                "attributes": {
                                                    "values": [
                                                        {
                                                            "attributes": {
                                                                "sourceName": "Source",
                                                                "attributeName": "End_Date"
                                                            },
                                                            "type": "accountAttribute"
                                                        },
                                                        "01/01/2050"
                                                    ]
                                                },
                                                "type": "firstValid"
                                            },
                                            "inputFormat": "dd/MM/yyyy",
                                            "outputFormat": "ISO8601"
                                        },
                                        "type": "dateFormat"
                                    },
                                    "roundUp": true
                                },
                                "type": "dateMath"
                            },
                            "inputFormat": "yyyy-MM-dd'T'HH:mm'Z'",
                            "outputFormat": "ISO8601"
                        },
                        "type": "dateFormat"
                    }
                ]
            }
        },
        "secondDate": {
            "attributes": {
                "inputFormat": "yyyy-MM-dd'T'HH:mm'Z'",
                "outputFormat": "ISO8601",
                "input": {
                    "attributes": {
                        "expression": "now/d"
                    },
                    "type": "dateMath"
                }
            },
            "type": "dateFormat"
        },
        "operator": "lte",
        "positiveCondition": "true",
        "negativeCondition": "false"
    }
}

This works for me. Try this. Thanks!! :smiling_face:

Thank you for your answer Gokul. We tried exactly as you said, and it prints the dates well. But still, when it comes the time to comapre them, it always returns false (meaning that today is lower than a date in the past). We can’t understand what is happening.

can you share any screenshots by hiding the sensitive data’s. It will be helpful to understand what’s happening. As, the recent transform which I sent works fine for me as per the requirement you sent.

I need, the format of input date as well

We kinda found the problem. We do a firstValid to search in 2 sources, when we remove the firstValid, it works as intended. Do you know why could this be?

 "quarantineGreater6M": {
            "type": "dateCompare",
            "attributes": {
                "firstDate": {
                    "type": "firstValid",
                    "attributes": {
                        "values": [
                            {
                                "type": "dateMath",
                                "attributes": {
                                    "expression": "+6M",
                                    "roundUp": true,
                                    "input": {
                                        "attributes": {
                                            "input": {
                                                "attributes": {
                                                    "values": [
                                                        {
                                                            "attributes": {
                                                                "sourceName": "Source1",
                                                                "attributeName": "endDate"
                                                            },
                                                            "type": "accountAttribute"
                                                        },
                                                        "01/01/2050"
                                                    ]
                                                },
                                                "type": "firstValid"
                                            },
                                            "inputFormat": "dd/MM/yyyy",
                                            "outputFormat": "ISO8601"
                                        },
                                        "type": "dateFormat"
                                    }
                                }
                            },
                            {
                                "type": "dateMath",
                                "attributes": {
                                    "expression": "+6M",
                                    "roundUp": true,
                                    "input": {
                                        "attributes": {
                                            "input": {
                                                "attributes": {
                                                    "values": [
                                                        {
                                                            "attributes": {
                                                                "sourceName": "Source2",
                                                                "attributeName": "endDate"
                                                            },
                                                            "type": "accountAttribute"
                                                        },
                                                        "01/01/2050"
                                                    ]
                                                },
                                                "type": "firstValid"
                                            },
                                            "inputFormat": "dd/MM/yyyy",
                                            "outputFormat": "ISO8601"
                                        },
                                        "type": "dateFormat"
                                    }
                                }
                            }
                        ]
                    }
                },
"secondDate": {
                    "attributes": {
                        "inputFormat": "yyyy-MM-dd'T'HH:mm'Z'",
                        "outputFormat": "yyyy-MM-dd'T'HH:mm'Z'",
                        "input": {
                            "attributes": {
                                "expression": "now/d"
                            },
                            "type": "dateMath"
                        }
                    },
                    "type": "dateFormat"
                },
                "operator": "lte",
                "positiveCondition": "true",
                "negativeCondition": "false"
            }
        },
1 Like

I couldn’t get you. Which place you are mentioning about?

and one more thing I noticed is,
while calculating the present_date if you are going to leave the result as like yyyy-MM-dd’T’HH:mm’Z’, you no need to use dateFormat transform here.

"secondDate": {
    "attributes": {
        "expression": "now/d"
    },
    "type": "dateMath"
}

When we remove this part of the code, it works fine and we don’t know why.

 {
                                "type": "dateMath",
                                "attributes": {
                                    "expression": "+6M",
                                    "roundUp": true,
                                    "input": {
                                        "attributes": {
                                            "input": {
                                                "attributes": {
                                                    "values": [
                                                        {
                                                            "attributes": {
                                                                "sourceName": "Source1",
                                                                "attributeName": "endDate"
                                                            },
                                                            "type": "accountAttribute"
                                                        },
                                                        "01/01/2050"
                                                    ]
                                                },
                                                "type": "firstValid"
                                            },
                                            "inputFormat": "dd/MM/yyyy",
                                            "outputFormat": "ISO8601"
                                        },
                                        "type": "dateFormat"
                                    }
                                }
                            },

Hi @rcgalvez ,
Is the date format for Source1 “dd/MM/yyyy”? That would create an error if its not in that format
Phil

2 Likes

I am trying to understand one thing.

You are using firstValid in two places.

"quarantineGreater6M": {
            "type": "dateCompare",
            "attributes": {
                "firstDate": {
                    "type": "firstValid",
                    "attributes": {
                        "values": [
                            {
                                "type": "dateMath",
                                "attributes": {
                                    "expression": "+6M",
                                    "roundUp": true,
                                    "input": {
                                        "attributes": {
                                            "input": {
                                                "attributes": {
                                                    "values": [
                                                        {
                                                            "attributes": {
                                                                "sourceName": "Source1",
                                                                "attributeName": "endDate"
                                                            },
                                                            "type": "accountAttribute"
                                                        },
                                                        "01/01/2050"
                                                    ]
                                                },
                                                "type": "firstValid"
                                            },
                                            "inputFormat": "dd/MM/yyyy",
                                            "outputFormat": "ISO8601"
                                        },
                                        "type": "dateFormat"
                                    }
                                }
                            },
                            {
                                "type": "dateMath",
                                "attributes": {
                                    "expression": "+6M",
                                    "roundUp": true,
                                    "input": {
                                        "attributes": {
                                            "input": {
                                                "attributes": {
                                                    "values": [
                                                        {
                                                            "attributes": {
                                                                "sourceName": "Source2",
                                                                "attributeName": "endDate"
                                                            },
                                                            "type": "accountAttribute"
                                                        },
                                                        "01/01/2050"
                                                    ]
                                                },
                                                "type": "firstValid"
                                            },
                                            "inputFormat": "dd/MM/yyyy",
                                            "outputFormat": "ISO8601"
                                        },
                                        "type": "dateFormat"
                                    }
                                }
                            }
                        ]
                    }
                },
"secondDate": {
                    "attributes": {
                        "inputFormat": "yyyy-MM-dd'T'HH:mm'Z'",
                        "outputFormat": "yyyy-MM-dd'T'HH:mm'Z'",
                        "input": {
                            "attributes": {
                                "expression": "now/d"
                            },
                            "type": "dateMath"
                        }
                    },
                    "type": "dateFormat"
                },
                "operator": "lte",
                "positiveCondition": "true",
                "negativeCondition": "false"
            }
        },

You are using a firstValid if there is no value found in source1’s endDate. So, in which scenario, the transform checks for source2?

Also check the input date once and check the format is in dd/MM/yyyy.
Check the user exists in source1 and source2 as well.

I would recommend you, if you are checking the date in source2 if there is no date found in source one, use

{
    "type": "dateMath",
    "attributes": {
        "expression": "+6M",
        "roundUp": true,
        "input": {
            "attributes": {
                "input": {
                    "attributes": {
                        "values": [
                            {
                                "attributes": {
                                    "sourceName": "Source1",
                                    "attributeName": "endDate"
                                },
                                "type": "accountAttribute"
                            },
                            {
                                "attributes": {
                                    "sourceName": "Source2",
                                    "attributeName": "endDate"
                                },
                                "type": "accountAttribute"
                            },
                            "01/01/2050"
                        ]
                    },
                    "type": "firstValid"
                },
                "inputFormat": "dd/MM/yyyy",
                "outputFormat": "ISO8601"
            },
            "type": "dateFormat"
        }
    }
},
1 Like

It was this! The date on that source was in another format. Thank you so much for pointing that out.

3 Likes

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.