topic Re: Split date and time in QlikView

Split date and time

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

Good Day,

I have an issue where by my date and time is currently combined into one field

e.g

01 April 2018, 1:18:55PM

Field name: Time (extract from excel)

i need to split this into two columns eg:

TIme Date

1:18:55PM 01 April 2018

the time field will not be required, however the date will be used in several charts ,

Main Question:

How do i split the date and time?

How can align the dates to only be Jan,Feb,March..... (as right it is Jan,Jan,Jan,Jan,Feb,Feb,Feb,March,March,April....)

Any suggestions would be really appreciated

Thanks

Re: Split date and time

Colin-Albert — Tue, 04 Dec 2018 13:32:15 GMT

These blogs should help.

https://community.qlik.com/t5/Qlik-Design-Blog/Get-the-Dates-Right/ba-p/1476178
https://community.qlik.com/t5/Qlik-Design-Blog/The-Master-Time-Table/ba-p/1469392
https://community.qlik.com/t5/Qlik-Design-Blog/The-Master-Calendar/ba-p/1471527

Re: Split date and time

sunny_talwar — Tue, 04 Dec 2018 13:48:20 GMT

You can try this

Date(Floor(TimeStamp#(Time, 'DD MMM YYYY, h:mm:ssTT')), 'DD MMM YYYY') as Date,
Time(Frac(TimeStamp#(Time, 'DD MMM YYYY, h:mm:ssTT')), 'h:mm:ssTT') as Time,

Re: Split date and time

juliensaintmaxent3113 — Tue, 04 Dec 2018 13:52:09 GMT

hello,

to split date and time use the function subfield:

subfield(Yourfield,',',-1) for date

subfield(Yourfield,',',1) for Time

and if you don't want to create master calendar (it's recommended) you can try floor(month(date)), i think it works

Bye

Re: Split date and time

hopkinsc — Tue, 04 Dec 2018 14:06:31 GMT

How about the attached?

Re: Split date and time

CreoLTD112 — Wed, 05 Dec 2018 06:06:16 GMT

This could work

However the only issue is that my date range (subfield Time) is extensively long - e.g: 01 April 2018, 1:18:55PM,01 April 2018, 1:18:55PM,01 April 2018, 1:18:55PM,01 April 2018, 1:18:55PM,01 April 2018, 1:18:55PM, 05 May 2018, 2:45:11,05 May 2018, 2:45:11,05 May 2018, 2:45:11

basically there are over 10000 values within the date range ,is there a method to merge all these values rather than adding them one by one?

Re: Split date and time

CreoLTD112 — Wed, 05 Dec 2018 06:10:02 GMT

essentially i need the following transaction

chart need to look up the date- and return all values within that month, the months are from Jan to Nov

Re: Split date and time

hopkinsc — Wed, 05 Dec 2018 11:31:17 GMT

Hi, do you have an example you could post?