Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

pskumars
New Contributor II

Duration text to Seconds

HI ,

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

listbox.JPG

1 Solution

Accepted Solutions
ahaahaaha
Honored Contributor

Re: Duration text to Seconds

Hi,

Try (look attached file)

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


Result

1.jpg


Regards,

Andrey

5 Replies
henrikalmen
Contributor

Re: Duration text to Seconds

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;"

ahaahaaha
Honored Contributor

Re: Duration text to Seconds

Hi,

Try (look attached file)

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


Result

1.jpg


Regards,

Andrey

Re: Duration text to Seconds

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 ];

Capture.JPG

Re: Duration text to Seconds

Hi,

another example: Re: Convert string to the timestamp

hope this helps

regards

Marco

Re: Duration text to Seconds

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.