No empty fields for dates
Answered
Hello,
What formula do i need to put in my calculated field with date range columns please. I have done what i normally do for other text columns but cant seem to make this work with a date field. I have tried putting a date in see attached and still get the same error.
The same question 
The problem is your case statement need the if and else data types to be the same
If you use a Freehand sql you can convert 'on the fly' for example
case when to_char(date_issued) is null then 'No Date' else to_char(date_issued) end
The problem is your case statement need the if and else data types to be the same
If you use a Freehand sql you can convert 'on the fly' for example
case when to_char(date_issued) is null then 'No Date' else to_char(date_issued) end
But the if is issued_date and the else is issued_date? They are the same? It’s the then that it seems to struggle with I think.
Tabitha Grooby
Business Systems Manager
HB Projects Ltd.,
Merrydale House,
Roydsdale Way,
Bradford - W. Yorkshire,
BD4 6SE
T:(01274) 474 080 M: 07964 027795
Bradford | Livingston | Gatwick
[ Twitter ] [ LinkedIn ] [ Website ]
Sent from my mobile device – please excuse any autocorrect errors
Confidentiality Notice:-
The information contained in this message is confidential and intended for the addressee only.
If you have received this message in error or there are problems with it, please notify the originator
immediately. The unauthorized use, disclosure copying or alteration of this message and its attachments
is strictly forbidden.
Registered in UK|Number 3685752
Registered Address: Merrydale House, Roydsdale Way, Bradford BD4 6SE
Please consider the environment before printing this e-mail
On 10 Apr 2018, at 16:36, Yellowfin Support <support@yellowfin.bi> wrote:
But the if is issued_date and the else is issued_date? They are the same? It’s the then that it seems to struggle with I think.
Tabitha Grooby
Business Systems Manager
HB Projects Ltd.,
Merrydale House,
Roydsdale Way,
Bradford - W. Yorkshire,
BD4 6SE
T:(01274) 474 080 M: 07964 027795
Bradford | Livingston | Gatwick
[ Twitter ] [ LinkedIn ] [ Website ]
Sent from my mobile device – please excuse any autocorrect errors
Confidentiality Notice:-
The information contained in this message is confidential and intended for the addressee only.
If you have received this message in error or there are problems with it, please notify the originator
immediately. The unauthorized use, disclosure copying or alteration of this message and its attachments
is strictly forbidden.
Registered in UK|Number 3685752
Registered Address: Merrydale House, Roydsdale Way, Bradford BD4 6SE
Please consider the environment before printing this e-mail
On 10 Apr 2018, at 16:36, Yellowfin Support <support@yellowfin.bi> wrote:
The case statement is an 'if then' so from your 'Built' case you want to put the value No Date if the date_issued is null
to do that using case both the result if date_issued is not null and if it is null must be the same data type (e.g. DATE or CHAR etc) to make that happen as 'No Date' cannot convert to a date value you have to convert the date_issued (from I assumed DATE format) into a character string using the to_char function.
there should be no problem using the same data field for both the if field and the else field as all you are doing is making it 'No Date' when the is no date_issued value.
Are you still getting the same error using the freehand sql?
The case statement is an 'if then' so from your 'Built' case you want to put the value No Date if the date_issued is null
to do that using case both the result if date_issued is not null and if it is null must be the same data type (e.g. DATE or CHAR etc) to make that happen as 'No Date' cannot convert to a date value you have to convert the date_issued (from I assumed DATE format) into a character string using the to_char function.
there should be no problem using the same data field for both the if field and the else field as all you are doing is making it 'No Date' when the is no date_issued value.
Are you still getting the same error using the freehand sql?
Hi Tabitha,
I have tried the same as you and don't get that error:
and the column 'boardingtime2' I'm using is definitely a datetime and not a string:
I am using the latest build of 7.4 and the database is SQL Server.
Please tell me which build of 7.2 you are using (Administration->System Information->Build)
and also which database you are querying and I will investigate further.
regards,
David
Hi Tabitha,
I have tried the same as you and don't get that error:
and the column 'boardingtime2' I'm using is definitely a datetime and not a string:
I am using the latest build of 7.4 and the database is SQL Server.
Please tell me which build of 7.2 you are using (Administration->System Information->Build)
and also which database you are querying and I will investigate further.
regards,
David
I am on Oracle 11.2 and YF 7.1 and I get the error initially described using the original statement
Just had a bit of a play and on 7.35 with Oracle the original statement is valid!
I guess that somewhere between 7.1 and 7.35 the statement is explicitly converted by Yellowfin
I am on Oracle 11.2 and YF 7.1 and I get the error initially described using the original statement
Just had a bit of a play and on 7.35 with Oracle the original statement is valid!
I guess that somewhere between 7.1 and 7.35 the statement is explicitly converted by Yellowfin
Hi Bernard,
you are correct, the issue was fixed for 7.35 and 7.4.
Also, the issue was not specific for Oracle.
regards,
David
Hi Bernard,
you are correct, the issue was fixed for 7.35 and 7.4.
Also, the issue was not specific for Oracle.
regards,
David
Hello, we have now had an upgrade to 7.4. Which is great news.
I have put this formula into my calculated field
It has worked:
but I now cant seem to change the date format as it thinks it is text?
where as the standard column next to it gives me these options
what have I missed please?
Hello, we have now had an upgrade to 7.4. Which is great news.
I have put this formula into my calculated field
It has worked:
but I now cant seem to change the date format as it thinks it is text?
where as the standard column next to it gives me these options
what have I missed please?
Hi Tabitha,
yes I can see what's happening, because databases don't allow you to substitute different data types within a CASE statement, Yellowfin has converted the date to a string and then that's why you're seeing the format options for a string rather than a date.
I can think of 2 ways around this:
1) You could use the Format Type of "Freehand SQL" (instead of what you're currently using, "Simple") and convert the hard-coded string date in the formula as follows:
Incidentally, the above syntax is for a SQL Server database, I'm not sure which type of DBMS you are using, you may have to use different syntax if it's not SQL Server.
2) You could create exactly the same calculated field (as you've already got) in the view rather than the report, and then apply a Text To Timestamp or Text To Date converter:
(such converters only exist at the view level, not in reports)
I hope this helps, please let me know how you get on with it.
regards.
David
Hi Tabitha,
yes I can see what's happening, because databases don't allow you to substitute different data types within a CASE statement, Yellowfin has converted the date to a string and then that's why you're seeing the format options for a string rather than a date.
I can think of 2 ways around this:
1) You could use the Format Type of "Freehand SQL" (instead of what you're currently using, "Simple") and convert the hard-coded string date in the formula as follows:
Incidentally, the above syntax is for a SQL Server database, I'm not sure which type of DBMS you are using, you may have to use different syntax if it's not SQL Server.
2) You could create exactly the same calculated field (as you've already got) in the view rather than the report, and then apply a Text To Timestamp or Text To Date converter:
(such converters only exist at the view level, not in reports)
I hope this helps, please let me know how you get on with it.
regards.
David
Hi Tabitha,
just wondering how you are getting on with your CASE statement and dates?
regards,
David
Hi Tabitha,
just wondering how you are getting on with your CASE statement and dates?
regards,
David
Replies have been locked on this page!