topic Re: Date Conversion in Loading Script in QlikView

Date Conversion in Loading Script

marleygt — Tue, 04 Aug 2015 09:35:15 GMT

Hi all.

I've a problem with a data format.

What I need: to change format in loading script.

Actual format: numeric, i.e.:('42079')

Need to change in: 'DD/MM/YYYY;

In the beginning of loading script I've already set this: SET DateFormat='DD/MM/YYYY'; but is not working on that specific field.

I tryed to use lastly (cause I made some testing before posting):

Date#(DATDOC,'DD/MM/YYYY') as Data, (DATDOC is field name from original loading file)

Is not giving an error whilst loading, but date still remain in same numeric format '42xxx'

I found a lot of discussion concerning date format, but no one si fitting good for my case.

I see that if I use for instance, in a listbox properties, in number properties, "Override document settings" ad date with the needed format, is working perfectly. But I need to convert this in the loading and not locally in any listbox or instance I will create.

Any suggestion for performing that?

Thank you in advance.

Alex

Re: Date Conversion in Loading Script

puttemans — Tue, 04 Aug 2015 09:37:56 GMT

Try Date, without the #

Re: Date Conversion in Loading Script

jonathandienst — Tue, 04 Aug 2015 09:39:08 GMT

Qlikview dates are actually numeric values so there is no need to convert it. All that is missing is the formatting, so this should be sufficient:

Date(DATDOC) As Date,

Then it will be stored with the default date format and there is no need to override any doc settings.

Re: Date Conversion in Loading Script

Anonymous — Tue, 04 Aug 2015 09:39:19 GMT

there is just one character to much

write

Date(DATDOC,'DD/MM/YYYY') as Data

Data# Needs if a dateformat is different as Qlikview expects

in your case the number is a valid date Format and you Need to tell Qlivkiew to Show as your format

Re: Date Conversion in Loading Script

robert_mika — Tue, 04 Aug 2015 09:39:45 GMT

Date(DATDOC,'DD/MM/YYYY') as Data

Re: Date Conversion in Loading Script

rubenmarin — Tue, 04 Aug 2015 09:40:05 GMT

Hi Alex, Date#() is used to tell QV wich date format has the field wich is loading.

Date() (without #) is used to give format in QV.

So try using Date(Date#(DATDOC,'DD/MM/YYYY')) as Data

Btw, a date is a number internally, the format of the date is just the way to represent that number.

Re: Date Conversion in Loading Script

marleygt — Tue, 04 Aug 2015 09:46:44 GMT

Damn....was more easier then expected....

Thank you guys! Is working perfeclty...

Argh...I can't sign 3 right answer...I am going to flag the first one, cause it was the fastest right answer, but thank you all!

Re: Date Conversion in Loading Script

marleygt — Tue, 04 Aug 2015 09:48:03 GMT

Thank you Ruben for clarifying!