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

[shrutithakur297]

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

[sasiparupudi1 · Accepted solution]

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]

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]

[jonathandienst]

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]

Thanks it worked for me.

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