Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
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 |
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.
Just do this?
Date(Date#([Trigger Date],'MMM DD YYYY hh:mm:ss:fffTT'),'DD/MM/YYYY HH:MM:SS') as [Timestamp Required]
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.
Hi Sunny,
Thanks for your tips to differentiate between MM and mm..
Regards
Thanks Anil
Hi Sunny,
Can you explain me on this ? (1/86400)
To floor to the nearest second... so bascially truncate the micro-seconds