Convert string to the timestamp

[waszcma1]

Could you help me write the formula changing the string to the seconds?

so far I have managed to change the names to the seconds value but still how to count total of all.

The problem is that the null value is not stored as a 0

so instead of 0 Days 0 Hours 25 Minutes  there is only 25 Minutes

So the problem is how to convert string to the number and calculate total of seconds if we have for example:

1 Day 10 Minutes

4 Days 2 Hours 30 Minutes

30 Minutes

10 Hours  10 Minutes

and so on....

[MarcoWedel]

Hi,

one solution might be:

mapStr2Sec:
Mapping
LOAD String&If(IterNo()=2,'s'),
    '*'&Seconds&'+'
INLINE [
    String, Seconds
    Week, 604800
    Day, 86400
    Hour, 3600
    Minute, 60
    Second, 1
] While IterNo()<=2;

table1:
LOAD *,
    MapSubString('mapStr2Sec',task_sla.dv_duration)&'0' as TimeSecondsString,
    Evaluate(MapSubString('mapStr2Sec',task_sla.dv_duration)&'0') as TimeSeconds,
    Interval(Evaluate(MapSubString('mapStr2Sec',task_sla.dv_duration)&'0')/86400, 'd hh:mm:ss') as Time
Inline [
task_sla.dv_duration
1 Day 10 Minutes
4 Days 2 Hours 30 Minutes
30 Minutes
10 Hours  10 Minutes
2 Days 1 Hour 10 Minutes 5 Seconds
3 Minutes 1 Second
1 Week 10 Seconds
];

hope this helps

regards

Marco

[ksomosera10]

You could try to do this:

     Date(Date#([Time],'HH:MM:ss'),'MM:SS') AS NewTime

I'm not on computer to try it, so you could try it for yourself.

[waszcma1 · Accepted solution]

marcowedel‌  your solution helps me to change the way of thinking

I have created one line formula in script:

Work as I wanted thanks for help !

[MarcoWedel]

Good to hear that you found a solution.

Please close your thread:

Qlik Community Tip: Marking Replies as Correct or Helpful

thanks

regards

Marco