Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a field in my data which will most often be a date, but will sometimes be a string value.
How can I get either value accepted?
If I load:
Field as DateorString
and the value is (e.g.) 22/05/2017 then QV turns it into 42877
But, if I load
Date(Field, 'DD/MM/YYYY') as DateorString
then whilst 22/05/2017 is shown correctly, any place where the value was actually a string doesn't get shown!
I've also tried:
Text(Field) as DateorString
but that still converts the date into a 5-digit value.
I'm only planning on using the date as a string anyway, but it needs to show in its correct format.
In that case Text(Field) should have worked. Sometimes, source data might just hold the numeric values (for proper date values) and that could led to show you five-digit values as you said. To handle that, you can try like:
If( Isnum(Field), Date(Field), text(Field)) as DateorString
I assume you have got dates in various formats in the source. If that is the case, best way to convert them to a unified format using below logic:
Date(Alt(Field, Date#(Field, 'PossibleFormat1'),Date#(Field, 'PossibleFormat2'), ..., Date#(Field, 'PossibleFormatN')) as Date
Hi Tresesco,
Thanks for your response.
But on the contrary - I don't want them as a unified format!
Basically, the field allows the user (who inputs the data onto a spreadsheet) to define at what point in the year they are submitting data.
I want them to be able to give a date value if they choose, or a string value if they choose - and for it to display exactly as they have input it.
So one user might write '22/05/2017' and another user might write '8th Submission'.
So now, in the 'DateorString' field I would like to display:
22/05/2017
8th Submission
Did you load from Excel?
Thus in the underlying program, the date is not a text, but a numeric value and only displayed as text.
You may try to use the ALT-functionality in reading and converting the dates.
Peter
In that case Text(Field) should have worked. Sometimes, source data might just hold the numeric values (for proper date values) and that could led to show you five-digit values as you said. To handle that, you can try like:
If( Isnum(Field), Date(Field), text(Field)) as DateorString
may be you can try something like
=if(isnum(Field),date(Field,'DD/MM/YYYY'),
if(istext(Field),text(Field)))
Hi Jessica,
QV recognises Field as Date Field (Dual)
Text(DateorString) -> 22/05/2017
Num(DateorString) -> 42877
I suggest You LOAD without change ed in Front End use always Text(DateorString)
If field contains '8th Submission' You see 8th Submission.
Regards.
Antonio
Thanks for all your responses.
I think it was the problem that Excel was storing it as numeric (which I hadn't realised!).
The solution of:
If( Isnum(Field), Date(Field), text(Field)) as DateorString
works perfectly.