I'm receiving errors when trying to set a Delta Filter. The column is a DATETIME column, but the event collection tries to convert the Datetime field into datetime and is receiving the error "Field not found 'ekes.datetime' "

Hi Dallas,

Are you extracting from an SAP environment? I have never heard of fields 'datetime' and 'bi_last_updated' in EKES.

In any case, when it comes to Delta extraction, I rather use the Delta Filter Statement. In my extraction of EKES, it is setup with this statement: ERDAT >= <%=ekesLastLoadDate%>

where <%=ekesLastLoadDate%> is a dynamic parameter of type DATE defined at level of the extraction job, using a global parameter which contains the generic extraction start date. Every time the extraction runs, this <%=ekesLastLoadDate%> is updated and used to extract the latest daily delta entries.

regards

Marc

The environment is SAP but the data is extracted from SAP to Snowflake. Celonis is then extracting the data from Snowflake. The field bi_last_updated is a datetime data type field that is added to the data set when it is extracted from SAP to Snowflake.

In the error message "Field not found 'ekes.datetime' " there is no field in the extracted columns named datetime. What I think is happening is the delta filter is trying to convert it to a datetime field and Celonis is interpreting the result as a column, but it doesn't exist. It seems like when a date field is used in a parameter in a delta filter, Celonis is trying to convert the result into a datetime datatype (e.g. screenshot 3 " convert(datetime, '2021-06-01 19:05:26:.0',21)). I believe this conversion attempt is what is throwing the error where convert(datetime is why Celonis is giving the error "Field not found 'ekes.datetime' ".

Using a parameter like <%=ekesLastLoadDate%> is how we are delta loading other tables so I can use that for EKES, however, it doesn't appear to be possible to use a field with the data type of DATETIME in a delta filter parameter in Celonis and I'm trying to understand why. Is it possible that since parameters can only be a data type of DATE and not DATETIME that it is automatically trying to convert the parameter and it's not able to perform this conversion?

This also poses a problem when using a parameter with the data type as TEXT because when the parameter is used, the following error message is received "Value 2021-06-01 19:05:26.0 could not be read as Text". I assume this is the case because it's trying to evaluate it in the delta filter against a DATETIME field and causing the error. Any further insight would be much appreciated.

OK, I don't know Snowflake, is it a kind of BI environment?

I would still use field ERDAT with a dynamic parameter rather than the the Snowflake field. Even if I see that it is possible to change such field (I found some entries in CDPOS) as it represents the confirmation creation date on vendor side (you could expect that it gets registered correctly from the first time, but apparently some people may have difficulties in transcripting dates ;-) )

Strange that you can't use the date from bi_last_updated because the delta parameter used in Celonis has the same format. I tried to extract the date from a date parameter field and got an error message because it is embedded, but it's interesting to see that the parameter is of type datetime :

ERDAT >= DATE(<%=ekesLastLoadDate%>) --> ERDAT >= DATE(DATE('2021-06-08 15:36:17'))

may be you should use the same field for both full and delta filter? Or could it be that the field content (I see .0 at the end, like if milliseconds where also registered in Snwoflake) is not recognized as a datetime?

Snowflake is a popular cloud data warehouse solution.

Looking at how the column is being recognized in the Celonis Table extraction Column Configuration tab, it is showing as a DATETIME data type. After it is extracted, the field is being recognized in the Celonis table is the data type of Timestamp. (Screenshots below)

Essentially is it even possible to use a Datetime/Timestamp field as a delta filter from a parameter? if not, is it possible to somehow convert it so at least the date from the timestamp can be used? Seems like it should work out of the box, but this is getting to be a bigger deal than I anticipated.

Hi Dallas,

sorry I won't be of further help unfortunately... You should raise a Support Ticket to get help from a Celonis expert directly in order to solve this issue.

If it is not possible, then consider using standard fields like ERDAT with the required offset to match the date in your 'bi_last_updated' field.

regards

Marc

Marc, thank you for your assistance. I have submitted a case with Celonis support to see if they can investigate.