topic Re: Problem loading date format correctly in script in QlikView

Problem loading date format correctly in script

Anonymous — Thu, 14 Mar 2013 16:12:17 GMT

I have a database table with the following format 'YYMMDD_HHMMSS' (as an example 111130_085114).

So my problem is to get qlikview to load this correctly in the script as a date and time format.

Anybody have a suggestion to solve this probably easy problem?

Re: Problem loading date format correctly in script

Thu, 14 Mar 2013 16:20:32 GMT

Try with this:

=date(date#(left('111130_085114', 6) , 'YYMMDD') + Time#(right('111130_085114', 6), 'hhmmss'), 'DD/MM/YYYY hh:mm:ss TT')

Replace '111130_085114' to a date field.

Regards,

Ricardo

Re: Problem loading date format correctly in script

manideep78 — Thu, 14 Mar 2013 16:21:23 GMT

Try this

Date(Date#(Field_Name,'YYMMDD_hhmmss'),'DD-MM-YYYY hh:mm:ss') as Field_Name

Re: Problem loading date format correctly in script

rustyfishbones — Thu, 14 Mar 2013 21:26:57 GMT

Hi Try this,

For your 'YYMMDD_HHMMSS' I called that field DateTime and I used the following in the Script

I used the below code to extract the Date

DATE(SUBFIELD(DateTime,'_',1),'DD/MM/YYYY') as Date,

And I used the below to extract the Time

TIME(SUBFIELD(DateTime,'_',2),'HH:MM:SS') as Time

I hope this works for you

Regards

Rusty!

Re: Problem loading date format correctly in script

MayilVahanan — Thu, 14 Mar 2013 21:46:48 GMT

Try like this

Load date(dateformat) as Dateformat,Hour(dateformat) As Hour, Timestamp(dateformat,'hh mm ss') as time;

Load Date(date#('111130_085114','YYMMDD_hhmmss'),'DD-MM-YYYY hh:mm:ss') as dateformat AutoGenerate 1;

instead of 111130_085114 give ur field name;

hope it helps

Re: Problem loading date format correctly in script

Anonymous — Fri, 15 Mar 2013 13:02:13 GMT

Hi Ricardo,

I tried your solution first in my script where I load my Sql data but realised that was not supposed to be here. Instead I created a List box and chose expresion as Field and edited as you suggested:

=date(date#(Left(line_id,6), 'YYMMDD') + Time#(right(line_id, 6), 'hhmmss'), 'DD.MM.YYYY hh:mm:ss')

And this work fine...

111130_083241 looks like 30.11.2011 08:32:41

I would like to do the convertion in the script as possible (don't know if it is possible loading data from a sql source) but this will do for now.

Thanks