topic Re: QlikView Timestamp Format in QlikView

QlikView Timestamp Format

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

How to do conversion data known as TriggerDate into Timestamp in Qlik View?

I already try by using below script but no result for Timestamp required

Data:
NoConcatenate
LOAD Distinct * Inline [
Trigger Date
Jul 18 2009 6:08:45:570PM
Jul 18 2009 6:28:48:423PM
Jul 18 2009 6:29:31:456PM
Jul 18 2009 6:30:41:183PM
Jul 18 2009 6:33:15:623PM
Jul 18 2009 6:52:43:853PM
Jul 18 2009 6:53:00:653PM
Jul 18 2009 6:54:46:026PM
Jul 18 2009 10:00:30:806PM
];

TimestampTest:
NoConcatenate
LOAD
[Trigger Date],
Timestamp(Timestamp#(left([Trigger Date],20),'MMM DD YYYY HH:MM:SS'),'DD/MM/YYYY HH:MM:SS') as [Timestamp Required] Resident Data;

DROP Tables Data;

TriggerDate	Timestamp Required
Jul 18 2009 6:08:45:570PM	18/07/2009 18:08:45
Jul 18 2009 6:28:48:423PM	18/07/2009 18:28:48
Jul 18 2009 6:29:31:456PM	18/07/2009 18:29:31
Jul 18 2009 6:30:41:183PM	18/07/2009 18:30:41
Jul 18 2009 6:33:15:623PM	18/07/2009 18:33:15
Jul 18 2009 6:52:43:853PM	18/07/2009 18:52:43
Jul 18 2009 6:53:00:653PM	18/07/2009 18:53:00
Jul 18 2009 6:54:46:026PM	18/07/2009 18:54:46
Jul 18 2009 10:00:30:806PM	18/07/2009 22:00:30

Re: QlikView Timestamp Format

Anil_Babu_Samineni — Wed, 24 Jan 2018 10:01:11 GMT

Just do this?

Date(Date#([Trigger Date],'MMM DD YYYY hh:mm:ss:fffTT'),'DD/MM/YYYY HH:MM:SS') as [Timestamp Required]

Re: QlikView Timestamp Format

sunny_talwar — Wed, 24 Jan 2018 12:23:36 GMT

Try this

Data:

NoConcatenate

LOAD DISTINCT * INLINE [

Trigger Date

Jul 18 2009 6:08:45:570PM

Jul 18 2009 6:28:48:423PM

Jul 18 2009 6:29:31:456PM

Jul 18 2009 6:30:41:183PM

Jul 18 2009 6:33:15:623PM

Jul 18 2009 6:52:43:853PM

Jul 18 2009 6:53:00:653PM

Jul 18 2009 6:54:46:026PM

Jul 18 2009 10:00:30:806PM

];

TimestampTest:

NoConcatenate

LOAD [Trigger Date],

Timestamp(Floor(Timestamp#([Trigger Date],'MMM DD YYYY hh:mm:ss:fffTT'), (1/86400)), 'DD/MM/YYYY hh:mm:ss') as [Timestamp Required]

Resident Data;

DROP Tables Data;

Note that Minutes in QlikView is lower case m, rather than upper case M. Upper case M is used for months.

Re: QlikView Timestamp Format

mohdhaniff — Thu, 25 Jan 2018 01:22:13 GMT

Hi Sunny,

Thanks for your tips to differentiate between MM and mm..

Regards

Re: QlikView Timestamp Format

mohdhaniff — Thu, 25 Jan 2018 01:23:26 GMT

Thanks Anil

Re: QlikView Timestamp Format

ramcena306 — Thu, 25 Jan 2018 06:04:10 GMT

Hi Sunny,

Can you explain me on this ? (1/86400)

Re: QlikView Timestamp Format

sunny_talwar — Thu, 25 Jan 2018 09:58:12 GMT

To floor to the nearest second... so bascially truncate the micro-seconds