Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
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