Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Gopi_Eswaravaka
Contributor

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

2 Solutions

Accepted Solutions
Gopi_Eswaravaka
Contributor

Re: How to convert the bellow format to date formate

@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

Re: How to convert the bellow format to date formate

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

Re: How to convert the bellow format to date formate

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

Gopi_Eswaravaka
Contributor

Re: How to convert the bellow format to date formate

Thanks for the replay,

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

Re: How to convert the bellow format to date formate

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

Gopi_Eswaravaka
Contributor

Re: How to convert the bellow format to date formate

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

Re: How to convert the bellow format to date formate

So, all you want is to remove Days?

Gopi_Eswaravaka
Contributor

Re: How to convert the bellow format to date formate

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_Eswaravaka
Contributor

Re: How to convert the bellow format to date formate

@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

Re: How to convert the bellow format to date formate

Try this

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

View solution in original post

Gopi_Eswaravaka
Contributor

Re: How to convert the bellow format to date formate

Thank you sunny,

it working from frontend as I want