topic Re: Convert string to the timestamp in QlikView

Convert string to the timestamp

waszcma1 — Tue, 14 Feb 2017 21:40:04 GMT

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

Re: Convert string to the timestamp

MarcoWedel — Wed, 15 Feb 2017 00:25:27 GMT

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

Re: Convert string to the timestamp

ksomosera10 — Wed, 15 Feb 2017 01:48:18 GMT

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.

Re: Convert string to the timestamp

waszcma1 — Wed, 15 Feb 2017 08:39:12 GMT

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 !

Re: Convert string to the timestamp

MarcoWedel — Wed, 15 Feb 2017 23:45:05 GMT

Good to hear that you found a solution.

Please close your thread:

Qlik Community Tip: Marking Replies as Correct or Helpful

thanks

regards

Marco