topic Re: Timestamp conversions in QlikView

Timestamp conversions

Wed, 05 Feb 2014 14:23:45 GMT

Here's attached an example qvw that you can run on your machines.

It seems that the Timestamp only suports 17 characters and I need 23.

As how it is now it trims 6 characters (4 from the year, a dash and a month digit), but if I take off the milliseconds from the Date string (which consist in a point and 3 digits=4 characters) it only trims 6-4=2 characters from the Year.

InputTable:

LOAD * INLINE [

MDate, Contract_ID,Attribute_Name,Attribute_Value_Text

'2012-05-23 17:21:22.111', ball,color,red

'2012-05-23 17:21:22.111', ball,diameter,10 cm

'2012-05-23 17:21:22.111', ball,weight,100 g

'2014-05-23 17:21:22.111', box,color,black

'2014-05-23 17:21:22.111', box,height,16 cm

'2014-05-23 17:21:22.111', box,length,20 cm

'2014-05-23 17:21:22.111', box,weight,500 g

'2014-05-23 17:21:22.111', box,width,10 cm

];

/* resulting table (delimiter=tab):

object color diameter weight height length width

ball red 10 cm 100 g

box black 500 g 16 cm 20 cm 10 cm

GenTable:

Generic Load timestamp(MDate,'YYYY-MM-DD HH:MM:SS.FFF') as SDate, Contract_ID, Attribute_Name, Attribute_Value_Text as AttributeValue Resident InputTable;

ResultTable:

LOAD Distinct timestamp(MDate,'YYYY-MM-DD HH:MM:SS.FFF') as SDate, Contract_ID Resident InputTable;

Re: Timestamp conversions

martinpohl — Wed, 05 Feb 2014 14:32:36 GMT

use timestamp(MDate,'YYYY-MM-DD hh:mm:ss.fff') instead of

Re: Timestamp conversions

Wed, 05 Feb 2014 14:35:22 GMT

Didn't work. Have you tested on the attached qvw?

Re: Timestamp conversions

Anonymous — Wed, 05 Feb 2014 14:40:34 GMT

I don't see any problems in the attached file, except the one that Martin Pohl pointed out (M is for months, m is for minutes). No any trimming...

Can you explain in some other way what the problem is?

Re: Timestamp conversions

Wed, 05 Feb 2014 14:43:40 GMT

So on the plain table displayed on the example it shows the whole date?

Not on my machine.

Re: Timestamp conversions

agilos_mla — Wed, 05 Feb 2014 14:45:35 GMT

You have to ensure your settings for timestamp does not contain [] for millisecond, by default it is;

//SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss.fff';

Then use timestamp#(MDate,'YYYY-MM-DD hh:mm:ss.fff') to interpret the date string correctly.

Michael

Re: Timestamp conversions

Wed, 05 Feb 2014 14:47:34 GMT

Figured out. Just had to re-size manually the column by dragging and dropping.

I don't know why QV didn't auto adjust to the column size from the start!!! So much trouble because of a cosmetic thing!!!!!

Re: Timestamp conversions

Anonymous — Wed, 05 Feb 2014 14:48:31 GMT

Yes, it is correct:

2012-05-23 17:21:22.111

2014-05-23 17:21:22.111

I tried in QV 11:20 SR5 and in QV 8.50

The machine is Windows 7 64-bit

Re: Timestamp conversions

Wed, 05 Feb 2014 14:48:46 GMT

Also have tried that before and didn't work. I already figured out the problem.

Thanks.

Re: Timestamp conversions

Wed, 05 Feb 2014 14:51:51 GMT

Windows 7 64-bit here...

Re: Timestamp conversions

Wed, 05 Feb 2014 15:05:13 GMT

Michael Solomovich do you have any idea why in your machine you didn't have this problem?

Thanks,

Miguel

Re: Timestamp conversions

Anonymous — Wed, 05 Feb 2014 15:11:00 GMT

No idea. In fact it is strange how it looks on your machine. If a numeric field doesn't fit in the cell, it should not be trimmed, it should show ####.

The behavior (trimming) in you screenshot implies that it is text field. But it behaves as numeric for me.

Re: Timestamp conversions

Wed, 05 Feb 2014 15:32:35 GMT

You think we should scale this to some of the Gods here?

Re: Timestamp conversions

Anonymous — Wed, 05 Feb 2014 16:39:25 GMT

It depends on your life philosophy

I'm OK with the fact that there are things I don't know...

Re: Timestamp conversions

Wed, 05 Feb 2014 16:51:11 GMT

Well I'm not OK if another developer has to go to the same problems I had to... I like to think I can make the world better