topic Re: How to convert the bellow format to date formate in QlikView

How to convert the bellow format to date formate

Gopi_E — Sat, 16 Nov 2024 02:26:21 GMT

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

Re: How to convert the bellow format to date formate

sunny_talwar — Thu, 19 Sep 2019 12:52:57 GMT

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

Re: How to convert the bellow format to date formate

Gopi_E — Thu, 19 Sep 2019 13:19:33 GMT

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

sunny_talwar — Thu, 19 Sep 2019 13:21:34 GMT

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

Re: How to convert the bellow format to date formate

Gopi_E — Thu, 19 Sep 2019 13:24:10 GMT

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

sunny_talwar — Thu, 19 Sep 2019 13:42:06 GMT

So, all you want is to remove Days?

Re: How to convert the bellow format to date formate

Gopi_E — Thu, 19 Sep 2019 13:47:47 GMT

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

Re: How to convert the bellow format to date formate

Gopi_E — Thu, 19 Sep 2019 13:53:18 GMT

@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

Re: How to convert the bellow format to date formate

sunny_talwar — Thu, 19 Sep 2019 13:54:24 GMT

Try this

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

Re: How to convert the bellow format to date formate

Gopi_E — Thu, 19 Sep 2019 14:01:43 GMT

Thank you sunny,

it working from frontend as I want