topic Re: Date to string in script in QlikView

Date to string in script

jessica_webb — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Date to string in script

tresB — Fri, 30 Jun 2017 09:23:10 GMT

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

Re: Date to string in script

jessica_webb — Fri, 30 Jun 2017 09:27:56 GMT

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

Re: Date to string in script

prieper — Fri, 30 Jun 2017 09:29:50 GMT

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

Re: Date to string in script

tresB — Fri, 30 Jun 2017 09:36:28 GMT

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

Re: Date to string in script

Kushal_Chawda — Fri, 30 Jun 2017 09:37:00 GMT

may be you can try something like

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

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

Re: Date to string in script

antoniotiman — Fri, 30 Jun 2017 09:41:34 GMT

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

Re: Date to string in script

jessica_webb — Fri, 30 Jun 2017 11:40:32 GMT

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.