topic Re: Duration text to Seconds in QlikView

Duration text to Seconds

pskumars — Tue, 27 Jun 2017 06:38:53 GMT

HI ,

I have a list box duration values which was a string and i need to convert in seconds at

Re: Duration text to Seconds

henrikalmen — Tue, 27 Jun 2017 07:02:47 GMT

It can be done in script, this will enable you to show the field duration as text or as a numeric value, where the value is the amount of seconds.

durationtable:

load dual(duration, durationSeconds) as duration;

load duration,

(days * 24 * 60 * 60) + (hours * 60 * 60) + (minutes * 60) + (seconds) as durationSeconds

;

load duration,

days,

right(hours, len(hours)-index(hours, ' ', SubStringCount(hours, ' '))) as hours,

right(minutes, len(minutes)-index(minutes, ' ', SubStringCount(minutes, ' '))) as minutes,

right(seconds, len(seconds)-index(seconds, ' ', SubStringCount(seconds, ' '))) as seconds

;

load duration,

if(index(duration, 'Day'), trim(subfield(duration, 'Day', 1)), 0) as days,

if(index(duration, 'Hours'), trim(subfield(duration, 'Hours', 1)), 0) as hours,

if(index(duration, 'Minutes'), trim(subfield(duration, 'Minutes', 1)), 0) as minutes,

if(index(duration, 'Seconds'), trim(subfield(duration, 'Seconds', 1)), 0) as seconds

;

load * inline [

duration

2 Days 14 Hours 45 Minutes

21 Seconds

6 Hours 52 Minutes

1 Day 2 Hours 52 Minutes 3 Seconds

];

If you want separate text fields and numeric fields you can just remove the line "load dual(duration, durationSeconds) as duration;"

Re: Duration text to Seconds

ahaahaaha — Tue, 27 Jun 2017 07:18:18 GMT

Hi,

Try (look attached file)

Evaluate(Replace(Replace(Replace(Replace(Replace(Data, ' Days', '*86400'), ' Hours', '*3600'), ' Minutes', '*60'), ' Seconds', ''), ' ', '+'));

Result

Regards,

Andrey

Re: Duration text to Seconds

Kushal_Chawda — Tue, 27 Jun 2017 07:23:37 GMT

Time:

LOAD *,

interval(Seconds/86400,'hh:mm:ss') as Time;

LOAD *,

if(wildmatch(lower(SubField(Duration,' ',2)),'*year*'),SubField(Duration,' ',1)*31104000 +

SubField(Duration,' ',3)*2592000+SubField(Duration,' ',5)*86400+SubField(Duration,' ',7)*3600+

SubField(Duration,' ',9)*60+SubField(Duration,' ',11),

if(wildmatch(lower(SubField(Duration,' ',2)),'*month*'),

SubField(Duration,' ',1)*2592000+SubField(Duration,' ',3)*86400+SubField(Duration,' ',5)*3600+

SubField(Duration,' ',7)*60+SubField(Duration,' ',9),

if(wildmatch(lower(SubField(Duration,' ',2)),'*day*'),

SubField(Duration,' ',1)*86400+SubField(Duration,' ',3)*3600+

SubField(Duration,' ',5)*60+SubField(Duration,' ',7),

if(wildmatch(lower(SubField(Duration,' ',2)),'*hour*'),

SubField(Duration,' ',1)*3600+SubField(Duration,' ',3)*60+SubField(Duration,' ',5),

if(wildmatch(lower(SubField(Duration,' ',2)),'*minute*'),

SubField(Duration,' ',1)*60+SubField(Duration,' ',3),

if(wildmatch(lower(SubField(Duration,' ',2)),'*second*'),

SubField(Duration,' ',1))))))) as Seconds

Inline [

Duration

1 Year 11 Months 11 Days 1 Hour 11 Minutes 30 Seconds

2 Months 3 Days 1 Hour 9 Minutes 29 Seconds

3 Days 18 Hours 24 Minutes 35 Seconds

19 Minutes 39 Seconds

1 Day 18 Hours 19 Minutes 36 Seconds

30 Seconds

14 Hours 22 Minutes 38 Seconds ];

Re: Duration text to Seconds

MarcoWedel — Tue, 27 Jun 2017 23:54:47 GMT

Hi,

another example: Re: Convert string to the timestamp

hope this helps

regards

Marco

Re: Duration text to Seconds

Kushal_Chawda — Wed, 28 Jun 2017 07:11:46 GMT

pskumars When you have values like, 1 Day, 1 Month, 1 Minute, then I think this solution might not work. Also in future you will have the more data in which there might be possibility of getting strings like 1 Year as well.Please refer to my solution in that case.