Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 

Qliksense General field(01/Apr/18 7:30 PM) with date to two new fields with date and time captured?

DATA SOURCE: CSV FILE


FIELD NAME: Created

VALUE: 01/Apr/18 7:30 PM


1. Extract Date   DD/MM/YYYY

2. Extract Time hh:mm

3. Extract Month MMM


I am new to Qliksense and I am not able to work with data functions and how to cast or convert string to date time month information.


I have tried all the possible solutions tutorials available online.


Thanks for helping in advance it will be very beneficial.


ST

1 Solution

Accepted Solutions
Master III
Master III

Date

=Date(Floor(Timestamp#('01/Apr/18 7:30 PM','DD/MMM/YY h:mm TT')),'DD/MM/YYYY')

hh:mm

=Time(Frac(Timestamp#('01/Apr/18 7:30 PM','DD/MMM/YY h:mm TT')),'hh:mm')

Month

=Date(Floor(Timestamp#('01/Apr/18 7:30 PM','DD/MMM/YY h:mm TT')),'MMM')

View solution in original post

4 Replies
Master III
Master III

Date

=Date(Floor(Timestamp#('01/Apr/18 7:30 PM','DD/MMM/YY h:mm TT')),'DD/MM/YYYY')

hh:mm

=Time(Frac(Timestamp#('01/Apr/18 7:30 PM','DD/MMM/YY h:mm TT')),'hh:mm')

Month

=Date(Floor(Timestamp#('01/Apr/18 7:30 PM','DD/MMM/YY h:mm TT')),'MMM')

View solution in original post

Creator II
Creator II

Hi Sruti,

Please find the below code:

Extract Date:

DATE(DATE#(CREATED,'DD/MMM/YY hh:mm TT'),'DD/MM/YYYY') AS [Extract Date]


Extract Time:

TIME(TIME#(CREATED,'DD/MMM/YY hh:mm TT'),'hh:mm') AS [Extract Time]


Extract Month:

DATE(DATE#(CREATED,'DD/MMM/YY hh:mm TT'),'MMM') AS [Extract Month]

I agree with Sasidhar for the date and time using Floor and Frac, but for the month expression will get distinct month values (which will look the same) for each day of the month. So I recommend the following

Date

Date(Floor(Timestamp#(Created,'DD/MMM/YY h:mm TT')),'DD/MM/YYYY')

Time

Time(Frac(Timestamp#(Created,'DD/MMM/YY h:mm TT')),'hh:mm')

Month (dual month value)

Month(Timestamp#(Created,'DD/MMM/YY h:mm TT'))

MonthYear (dual MMM-yyyy value)

Date(MonthStart(Timestamp#(Created,'DD/MMM/YY h:mm TT')),'MMM-yyyy')

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

Thanks it worked for me.


Can you help with converting it to Timestamp so I can calculate the no. of hours elasped from today().