Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
pskumars
Partner
Partner

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
Master
Master

Hi,

Try (look attached file)

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


Result

1.jpg


Regards,

Andrey

View solution in original post

5 Replies
henrikalmen
Creator
Creator

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
Master
Master

Hi,

Try (look attached file)

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


Result

1.jpg


Regards,

Andrey

View solution in original post

Kush
MVP
MVP

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

MarcoWedel

Hi,

another example: Re: Convert string to the timestamp

hope this helps

regards

Marco

Kush
MVP
MVP

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.