topic Re: Convert alphanumeric uration into HH:MM:SS in QlikView

Convert alphanumeric uration into HH:MM:SS

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi ,

I have a excel having the time duration in alphanumeric (e.g. 1h30m , 2h) . I want to convert duration into HH:MM format .

Please help me on the same .

Thanks,

Vals

Re: Convert alphanumeric uration into HH:MM:SS

adamdavi3s — Thu, 16 Feb 2017 13:33:36 GMT

Hi Vals,

Not that easy to do, can you provide a sample dataset?

I am sure it can be done though

Re: Convert alphanumeric uration into HH:MM:SS

adamdavi3s — Thu, 16 Feb 2017 13:35:48 GMT

Tell a lie, I found an example of where I have done this before:

Please remember to mark this as helpful or the correct answer if I have helped you or answered your question.

This will help users identify the answers should they come across this thread in the future.

Re: Convert alphanumeric uration into HH:MM:SS

sdmech81 — Thu, 16 Feb 2017 13:38:45 GMT

PLss send sample data..Hope we need to use string functions to arrive at that

Sachin

Re: Convert alphanumeric uration into HH:MM:SS

Anonymous — Thu, 16 Feb 2017 14:08:18 GMT

Re: Convert alphanumeric uration into HH:MM:SS

Anonymous — Thu, 16 Feb 2017 14:10:02 GMT

This is the format of the xlsx and in qlikview workbook i want it in HH:MM format . I tried to use SUBFIELD and TRIM function to make it happen but unfortunately no result .

Thanks

Re: Convert alphanumeric uration into HH:MM:SS

adamdavi3s — Thu, 16 Feb 2017 14:10:49 GMT

Hi Vals,

Not a problem I can write this for you.

What happens when the time is < 1hour?

Re: Convert alphanumeric uration into HH:MM:SS

Anonymous — Thu, 16 Feb 2017 14:14:12 GMT

Hi Adam,

Actually its the master file given by user . So manually inserted data and time is not less than 1 hr each and every events are having duration more than 1 hr .

Re: Convert alphanumeric uration into HH:MM:SS

adamdavi3s — Thu, 16 Feb 2017 14:16:25 GMT

Here is my sample script which loads:

LOAD rowno() as id,*,

subfield(time,'h',1)&':'&if(len(subfield(time,'h',2))<1,00,subfield(time,'h',2)) as timestamp

;

LOAD * INLINE [

time

2h30

1h30

3h30

];

Re: Convert alphanumeric uration into HH:MM:SS

MarcoWedel — Sat, 18 Feb 2017 00:38:31 GMT

Hi,

maybe helpful:

Convert string to the timestamp

regards

Marco

Re: Convert alphanumeric uration into HH:MM:SS

Anonymous — Thu, 23 Feb 2017 12:51:45 GMT

Thanks , It worked but this will create problem further while calculating the Sum in minutes or in hours ..!

Re: Convert alphanumeric uration into HH:MM:SS

adamdavi3s — Fri, 24 Feb 2017 07:58:06 GMT

Not really as you have a timestamp field which is very easy to work with in Qlik!