I have a variable DateDeliveryOld that takes the last old value from the Delivery Date field, which is in String format (e.g. 20210211). Then I have a formula that should either output the DateDeliveryOld value in date format (TO_TIMESTAMP), else the current Delivery Date (EKET.EINDT):
CASE
WHEN <%=DateDeliveryOld%> IS NOT NULL
THEN CASE
WHEN <%=DateDeliveryOld%> LIKE 20%
THEN TO_TIMESTAMP(<%=DateDeliveryOld%>, YYYYMMDD)
ELSE PU_FIRST(EKPO, EKET.EINDT)
END
ELSE PU_FIRST(EKPO, EKET.EINDT)
END
Unfortunately the result I get is the error Day of month value is out of range 131. I thought I already prevented this by taking out null values and invalid dates (not starting with year 20).
I exported the data and converted to date in Excel, and following are the invalid dates. I would expect these to be captured in above formula. Is there anything else I should include in the formula to obtain valid dates?
Date_Delivery_old Case count
3256711
00000000 1
22010201 1
22010205 1
22020308 1
22021120 1
30300823 1
My Celonis version is 4.5.0.
Page 1 / 1
Hi @soppeneer
The issue is that the TO_TIMESTAMP is still executed on all inputs, doesnt matter if you have the CASE WHEN around it or not. What you need to do is, you have to remove the invalid strings already in the input to TO_TIMESTAMP:
I would assume that this should work.
Best,
David
The issue is that the TO_TIMESTAMP is still executed on all inputs, doesnt matter if you have the CASE WHEN around it or not. What you need to do is, you have to remove the invalid strings already in the input to TO_TIMESTAMP:
COALESCE (
TO_TIMESTAMP ( CASE WHEN <%=DateDeliveryOld%> LIKE '20%' THEN <%=DateDeliveryOld%> ELSE NULL END ),
PU_FIRST("EKPO", "EKET"."EINDT")
)
I would assume that this should work.
Best,
David
@d.becher Thanks for your feedback. I wasnt familiar with COALESCE, but it seems like a good fit. Unfortunately when trying your solution, Im getting the following error. Should I include another validation?
TABLE (COALESCE(TO_TIMESTAMP(CASE WHEN NaN LIKE 20% THEN NaN ELSE NULL END), PU_FIRST(EKPO, EKET.EINDT)) AS ) LIMIT 50 OFFSET 0 is not a valid PQL expression: Encountered " ( ( at line 1, column 17. Was expecting one of: FORMAT / <S_IDENTIFIER> , ) || <S_IDENTIFIER>
TABLE (COALESCE(TO_TIMESTAMP(CASE WHEN NaN LIKE 20% THEN NaN ELSE NULL END), PU_FIRST(EKPO, EKET.EINDT)) AS ) LIMIT 50 OFFSET 0 is not a valid PQL expression: Encountered " ( ( at line 1, column 17. Was expecting one of: FORMAT / <S_IDENTIFIER> , ) || <S_IDENTIFIER>
Hi @soppeneer
sorry, seems like I forgot to add the pattern in the TO_TIMESTAMP function:
But it also seems like your variable has the content NaN; I would expect it to be a string column.
Best
David
sorry, seems like I forgot to add the pattern in the TO_TIMESTAMP function:
COALESCE (
TO_TIMESTAMP ( CASE WHEN <%=DateDeliveryOld%> LIKE '20%' THEN <%=DateDeliveryOld%> ELSE NULL END, 'YYYYMMDD' ),
PU_FIRST("EKPO", "EKET"."EINDT")
)
But it also seems like your variable has the content NaN; I would expect it to be a string column.
Best
David
@d.becher The DeliveryDateOld variable is a static value defined as follows which can return a blank value which I suppose returns the NaN:
|| PU_LAST(EKPO, _CEL_P2P_ACTIVITIES.CHANGED_FROM, _CEL_P2P_ACTIVITIES.ACTIVITY_EN = Change Delivery Date on Schedule Line AND _CEL_P2P_ACTIVITIES.EVENTTIME < {D2021-01-01})
|| PU_LAST(EKPO, _CEL_P2P_ACTIVITIES.CHANGED_FROM, _CEL_P2P_ACTIVITIES.ACTIVITY_EN = Change Delivery Date on Schedule Line AND _CEL_P2P_ACTIVITIES.EVENTTIME < {D2021-01-01})
@soppeneer Then you should check what the value of your variable is and also exclude this in the CASE WHEN. However, your variable will only return one single value, so I do not understand your use case, because if this single value is NaN, it will always be NaN. But this problem should have occurred before in your original query I would assume.
Best
David
Best
David
Your suggestion worked after I changed the variable from static value to text/replacement.
Variable DateDeliveryOld (Text/Replacement):
|| PU_LAST(EKPO, _CEL_P2P_ACTIVITIES.CHANGED_FROM, _CEL_P2P_ACTIVITIES.ACTIVITY_EN = Change Delivery Date on Schedule Line AND _CEL_P2P_ACTIVITIES.EVENTTIME < {D2021-01-01})
Formula Date_Delivery_Old:
COALESCE(TO_TIMESTAMP(CASE WHEN <%=DateDeliveryOld%> LIKE 20% THEN <%=DateDeliveryOld%> END, YYYYMMDD), PU_FIRST(EKPO, EKET.EINDT))
Thanks!
Variable DateDeliveryOld (Text/Replacement):
|| PU_LAST(EKPO, _CEL_P2P_ACTIVITIES.CHANGED_FROM, _CEL_P2P_ACTIVITIES.ACTIVITY_EN = Change Delivery Date on Schedule Line AND _CEL_P2P_ACTIVITIES.EVENTTIME < {D2021-01-01})
Formula Date_Delivery_Old:
COALESCE(TO_TIMESTAMP(CASE WHEN <%=DateDeliveryOld%> LIKE 20% THEN <%=DateDeliveryOld%> END, YYYYMMDD), PU_FIRST(EKPO, EKET.EINDT))
Thanks!
Reply
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.