Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Gopi_E
Creator II
Creator II

How to convert the bellow format to date formate

Hi Qlikers,

@sunny_talwar  

I have bellow formate for converting to date, can you suggest any way to get the date formate

Duration
0 Days 00:00:00
1 Days 02:10:43
1 Days 03:41:38
1 Days 19:43:16
10 Days 01:28:59

Thanks in advance

Labels (1)
2 Solutions

Accepted Solutions
Gopi_E
Creator II
Creator II
Author

@sunny_talwar 

hi sunny tanks for giving your time 

I got the answer with bellow script, and is there any other way to do this please suggest me

Duration_Table:
LOAD * INLINE [
Duration
0 Days 00:00:00
1 Days 02:10:43
1 Days 03:41:38
1 Days 19:43:16
10 Days 01:28:59
];

Replace:
Load Duration,
Replace(Duration,'Days','') as replace_days
Resident Duration_Table;

DROP Table Duration_Table;

LOAD replace_days,
Date(Date#(dd,'D hh:mm:ss'),'dd hh:mm:ss') as TimeStamp
Resident dura;

DROP Table Replace;

Thank you

View solution in original post

sunny_talwar

Try this

=Interval(Interval#(Replace('1 Days 03:41:38', 'Days ', ''), 'D hh:mm:ss'), 'D hh:mm:ss')

View solution in original post

9 Replies
sunny_talwar

What should the output look like? I mean what would 0 Days 00:00:00 look like in Date format?

Gopi_E
Creator II
Creator II
Author

Thanks for the replay,

I want timestamp(D hh:mm: ss) from that field because I need to calculate duration and sum like that.

sunny_talwar

I am not sure I understand, what exactly do you mean?

Gopi_E
Creator II
Creator II
Author

Thank you sunny,

I want output formate as timestamp like bellow

10  00:00:00
1  02:10:43
1  03:41:38
1  19:43:16
10  01:28:59

Thank you

sunny_talwar

So, all you want is to remove Days?

Gopi_E
Creator II
Creator II
Author

yes but, for removing, I used replace function and result I will get

Duration
0 00:00:00
1  02:10:43
1 03:41:38
1  19:43:16
10  01:28:59

but problem is I tried to convert this text formate to Timestamp using timestamp# but it is not converting to timestamp

 

Gopi_E
Creator II
Creator II
Author

@sunny_talwar 

hi sunny tanks for giving your time 

I got the answer with bellow script, and is there any other way to do this please suggest me

Duration_Table:
LOAD * INLINE [
Duration
0 Days 00:00:00
1 Days 02:10:43
1 Days 03:41:38
1 Days 19:43:16
10 Days 01:28:59
];

Replace:
Load Duration,
Replace(Duration,'Days','') as replace_days
Resident Duration_Table;

DROP Table Duration_Table;

LOAD replace_days,
Date(Date#(dd,'D hh:mm:ss'),'dd hh:mm:ss') as TimeStamp
Resident dura;

DROP Table Replace;

Thank you

sunny_talwar

Try this

=Interval(Interval#(Replace('1 Days 03:41:38', 'Days ', ''), 'D hh:mm:ss'), 'D hh:mm:ss')
Gopi_E
Creator II
Creator II
Author

Thank you sunny,

it working from frontend as I want