topic Re: Date & Time in QlikView

Date & Time

Thu, 16 Jan 2014 09:52:06 GMT

Hi all

I am importing date fields from two different tables from a Firebird database.

Looking at the data types in the Firebird database, they are all just 'Date' rather than 'DateTime'.

However, the fields that I import from Table B result in the time portion of 00:00:00 being added, even though when I load them in to the final table I use the Floor() function.

Obviously, I can just format any data tables accordingly but I would rather have uniform data types, especially for dates.

Can anyone help?

Thanks

James

Re: Date & Time

jagan — Thu, 16 Jan 2014 10:03:30 GMT

Hi,

Format the dates using the

Date(DateField, 'MM/DD/YYYY')

Regards,

Jagan.

Re: Date & Time

sujeetsingh — Thu, 16 Jan 2014 10:14:07 GMT

yes go with Date functions to format them

Re: Date & Time

Thu, 16 Jan 2014 10:33:06 GMT

try according to this date format.

date(date#(datefieldtable1,'DD-MM-YYYY'),'DD/MM/YYYY')

date(date#(datefieldtable2,'MM-DD-YYYY'),'DD/MM/YYYY')

Re: Date & Time

Thu, 16 Jan 2014 11:59:06 GMT

Thanks for the posts.

Unfortunately, just using the Date function is not producing consistent results.

I am using this QlikView script to transform data imported from a Firebird database, so I think I need to understand if there are any implicit conversions happening.

Here is a typical transfomation statement that I am using for a date (as I don't want any zero length strings in the field)

If(DATE_SETUP = '', Null(), DATE_SETUP) AS LeadDate

Re: Date & Time

miikkaqlick — Thu, 16 Jan 2014 12:27:41 GMT

Hi!

I sometimes use Left-function to get rid of time:

DateTime = 2014/01/01 00:00:00

Left(DateTime,10) = 2014/01/01

You might need to convert this little more to match another date-type.

Br,

Miikka

Re: Date & Time

CELAMBARASAN — Thu, 16 Jan 2014 12:34:55 GMT

Please check the field is in date format or simply text?

Goto Document properties --> Tables --> Check the Tag for the Field "LeadDate". If tag has $number then probably it is in date format. Then use

If(DATE_SETUP = '', Null(), Date(DATE_SETUP)) AS LeadDate

No $number tag you need to convert it in to date format by using date# function.

To use date# function you need to analyse in which format it is getting loaded to qlikview.

MM/DD/YYYY or DD/MM/YYYY or DD-MM-YYYY or MM-DD-YYYY

If(DATE_SETUP = '', Null(), Date(Date#(DATE_SETUP, "MM/DD/YYYY"))) AS LeadDate

Re: Date & Time

Anonymous — Thu, 16 Jan 2014 12:37:55 GMT

Hi James,

can you please share only dates form you data,just two to three rows only.

other wise share the data formats you getting.

Thanks

SHAIK

Re: Date & Time

hic — Thu, 16 Jan 2014 15:45:31 GMT

The time portion is just formatting - added by either the ODBC or by QlikView in the load phase.

QlikView does not have any data types (See this article). If it is a number and you have used the floor function, you are all set. You should/could use a Date() function on top of it to get the formatting right, but this is almost just "cosmetics". All numeric evaluations and sorting are based on the number you created with the Floor() function.

So, if you want to debug, and see what goes on, format it as a number instead: Num(DATE_SETUP).

And the NULL test is better made with

If(Len(Trim(DATE_SETUP))>0, DATE_SETUP) AS LeadDate, or

If(IsNum(Date#(DATE_SETUP)), Date#(DATE_SETUP)) AS LeadDate

This way you also catch values that contain spaces.

HIC

Re: Date & Time

Anonymous — Fri, 14 Mar 2014 08:41:40 GMT

goodmorning. replying just not to open a new thread... i'm importing from a database where date and time are 2 different columns (i.e. for each day/date i have as many rows as records are acquired through the timeline); i'd like to concatenate i have to do calculations over another DB where the field "time" has all the parameters (DD/MM/YYYY hh:mm). how can i do that, i.e. not only concatenate characters but obtain a sort of numeric field to use with calculations? thanx

Re: Date & Time

hic — Fri, 14 Mar 2014 09:16:53 GMT

If you use the interpretation functions, you will get numeric values that you can add and subtract. Hence, you should use

Date#(DateField) + Time#(TimeField)

to get the correct timestamp number. Then you can wrap this in a formatting function to make it look good:

Timestamp( Date#(DateField) + Time#(TimeField), 'DD/MM/YYYY hh:mm' )

HIC

Re: Date & Time

Anonymous — Wed, 19 Mar 2014 08:29:08 GMT

tried, no result... " -" appears in the cells of the records...