Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Customers, Partners & Luminaries only: You're invited to a Data Analytics Roadmap session. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

Date to string in script

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.

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

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

View solution in original post

7 Replies
Highlighted
MVP
MVP

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

Creator III
Creator III

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

Highlighted
Master II
Master II

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

Highlighted
MVP
MVP

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

View solution in original post

Highlighted
MVP
MVP

may be you can try something like

=if(isnum(Field),date(Field,'DD/MM/YYYY'),

if(istext(Field),text(Field)))

Highlighted
Master III
Master III

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

Highlighted
Creator III
Creator III

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.