Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

shrutithakur297
Registered User

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
sasiparupudi1
Honored Contributor III

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

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')

4 Replies
sasiparupudi1
Honored Contributor III

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

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')

sumanta12
Contributor II

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

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]

MVP
MVP

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

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')

shrutithakur297
Registered User

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

Thanks it worked for me.


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

Community Browser