Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

converting timestamp to date and then to num

Hi Geniuses,

I am facing problem while converting timestamp to date

I have the timestap coming as text from source like below

 

4/6/2016 9:52 AM
4/6/2016 10:28 AM
4/6/2016 10:32 AM
4/6/2016 10:40 AM
4/6/2016 11:35 AM
4/6/2016 11:38 AM
4/6/2016 11:41 AM
4/6/2016 11:42 AM

I want to first convert it into date and then change that date into number

As i have to calculate the open days for the ticket

Thanks in advance

Shashank

1 Solution

Accepted Solutions
HirisH_V7
Master
Master

Hi,

May be like this,

Data:

LOAD *,

Date(Timestamp#(Field,'DD/MM/YYYY h:mm TT'),'DD/MM/YYYY') as Date,

Num(Date(Timestamp#(Field,'DD/MM/YYYY h:mm TT'),'DD/MM/YYYY')) as NumDate

INLINE [

    Field

    4/6/2016 9:52 AM

    4/6/2016 10:28 AM

    4/6/2016 10:32 AM

    4/6/2016 10:40 AM

    4/6/2016 11:35 AM

    4/6/2016 11:38 AM

   4/6/2016 11:41 AM

   4/6/2016 11:42 AM

];

Output:

Timestamp date  and date num-212442.PNG

HTH,

PFA,

Hirish

HirisH
“Aspire to Inspire before we Expire!”

View solution in original post

5 Replies
Chanty4u
MVP
MVP

=Date(DATE#(Datefield,'D/M/YYYY hh:ss T'   )    'DD/MM/YYYY') AS  new date.

What is ur required date format?

=Num(Date field)

HirisH_V7
Master
Master

Hi,

May be like this,

Data:

LOAD *,

Date(Timestamp#(Field,'DD/MM/YYYY h:mm TT'),'DD/MM/YYYY') as Date,

Num(Date(Timestamp#(Field,'DD/MM/YYYY h:mm TT'),'DD/MM/YYYY')) as NumDate

INLINE [

    Field

    4/6/2016 9:52 AM

    4/6/2016 10:28 AM

    4/6/2016 10:32 AM

    4/6/2016 10:40 AM

    4/6/2016 11:35 AM

    4/6/2016 11:38 AM

   4/6/2016 11:41 AM

   4/6/2016 11:42 AM

];

Output:

Timestamp date  and date num-212442.PNG

HTH,

PFA,

Hirish

HirisH
“Aspire to Inspire before we Expire!”
tamilarasu
Champion
Champion

Not sure your what you are trying to do. Try,

Num(Floor(Date#(Datefield,'M/D/YYYY h:mm TT')))

Chanty4u
MVP
MVP

Small change to my exp.

Data:

LOAD *,

num(Date(DATE#(Datefield,'D/M/YYYY hh:ss TT') ,'DD/MM/YYYY')) AS  new1

INLINE [

    Datefield

    4/6/2016 9:52 AM

    4/6/2016 10:28 AM

    4/6/2016 10:32 AM

    4/6/2016 10:40 AM

    4/6/2016 11:35 AM

    4/6/2016 11:38 AM

   4/6/2016 11:41 AM

   4/6/2016 11:42 AM];num.PNG

jagan
Luminary Alumni
Luminary Alumni

Hi,

Try like this

Floor(DATE#(Datefield,'M/D/YYYY hh:ss TT'   ) )


Regards,

Jagan.