Please help! :(
Can you please support me, I need a formula that calculate the days between to dates given certain conditions, so I express the query like this:
if ([Actual Vessel Departure Date/Time (GMT)] contains 'Not available') then ('Not Available') else (if ([Actual Vessel Arrival Date/Time (GMT)] contains 'In Transit') then ('In Transit') else (_days_between ([Actual Vessel Departure Date/Time (GMT)] , [Actual Vessel Arrival Date/Time (GMT)]))
But I am having the error QE-DEF-0260 parsing error before or near position 295 and show all the query until the end, so I cannot understand why it's telling the error.
thank you for your support
If you are doing _days_between two columns then the expectation is that both the columns are in date format so how can they contain character info like 'Not Available' ?
Not sure if it would work (as CognosTechie says, the field should both be dates really), but you could try something along the lines of:
case
when [Actual Vessel Departure Date/Time (GMT)] contains 'Not available' then 'Not Available'
when [Actual Vessel Arrival Date/Time (GMT)] contains 'In Transit' then 'In Transit'
else
cast(_days_between (cast([Actual Vessel Departure Date/Time (GMT)],date) , cast([Actual Vessel Arrival Date/Time (GMT)],date)),varchar(30))
end
Formatting the expression to make it a bit more readable, I think you're missing a closing bracket for the else. I'm a wee bit confused about what the data type of Actual Vessel Departure Date/Time (GMT). You seem to expect it to be a date-time but you also have text values. You might run into problems with _days_between as a consequence.
if ([Actual Vessel Departure Date/Time (GMT)] contains 'Not available')
then ('Not Available')
else (
if ([Actual Vessel Arrival Date/Time (GMT)] contains 'In Transit')
then ('In Transit')
else (
_days_between ([Actual Vessel Departure Date/Time (GMT)] , [Actual Vessel Arrival Date/Time (GMT)])
)
Should be
if ([Actual Vessel Departure Date/Time (GMT)] contains 'Not available')
then ('Not Available')
else (
if ([Actual Vessel Arrival Date/Time (GMT)] contains 'In Transit')
then ('In Transit')
else (
_days_between ([Actual Vessel Departure Date/Time (GMT)] , [Actual Vessel Arrival Date/Time (GMT)])
)
)
All possible outcomes of your expression must resolve to the same data type. You've got a character string being returned in some cases and an integer being returned in the final 'else' clause. You need to have every outcome resolve to the same thing, so all must be integers or all must be strings.
You can achieve this by casting the result of your _days_between expression to a varchar.
That's essentially what i was suggesting...haven't tried what I wrote though, so it might crash in flames as soon as anyone tries it :)