topic Re: Substract Dates in QlikView

Substract Dates

Wed, 03 Aug 2011 15:42:39 GMT

Hi Everybody.

I´m new in Qlikview.

I need help in order subtract two dates, i got this dates in two table fields call "Fecha de Apertura" and "Inicio de Afectación" the idea is to obtain something like "DD, hh:mm:ss" after the subtract, i use only [Fecha Apertura]-[Inicio de Afectacion] but it doesn´t work, how can i do??

Best Regards

Re: Substract Dates

Wed, 03 Aug 2011 15:49:04 GMT

What are you getting when you subtract the dates? A decimal number?

The result should be the number of days. To get it in days, minutes, etc... you will need to convert that yourself.

Here is an example for days/hours/minutes: Floor(([Fecha Apertura]-[Inicio de Afectacion])) & 'd ' & Floor(Fmod(([Fecha Apertura]-[Inicio de Afectacion]), 1) * 24) & 'h ' & Round(Fmod(Fmod(([Fecha Apertura]-[Inicio de Afectacion]), 1) * 24, 1) * 60) & 'm'

It should return in the format: 4d 3h 14m

Re: Substract Dates

Wed, 03 Aug 2011 16:32:29 GMT

Hi Kramelot, use something like this:

tmp:
LOAD * INLINE [
    FechaApertura, InicioAfectación
    05/01/2011 12:25:00, 01/01/2011 10:15:50
];
data:
load 
interval(FechaApertura-InicioAfectación,'DD, hh:mm:ss') as SubstractDate
resident tmp;

Hope this help

Re: Substract Dates

Wed, 03 Aug 2011 16:54:57 GMT

Thanks for your answer...

i used your suggestion but only appears d h m, without any value, and the date format is equal than the system.

I attached the QV document.

B. R

Re: Substract Dates

Wed, 03 Aug 2011 17:26:12 GMT

Hi.

I tried your suggestion but it doesn´t work.

Thanks

Re: Substract Dates

johnw — Wed, 03 Aug 2011 19:37:37 GMT

Your dates are not dates or timestamps. They are text fields. You can't subtract text fields.

You can probably read them in as timestamps by changing your TimestampFormat to match:

SET TimestampFormat='DD/MM/YYYY hh:mm:ss';

But I can't test because you're loading from a file you didn't attach.

You can't put a load with no source after the main load and expect anything to happen. That sort of expression needs to be in the main load.

interval([Fecha Apertura]-[Inicio de Afectacion|Inicio de la interrupcion de servicio],'DD, hh:mm:ss') as [Tiempo de Apertura],

And that's assuming it interprets the timestamps correctly with the fixed format. Otherwise it gets more complicated.

Substract Dates

Wed, 03 Aug 2011 20:02:24 GMT

Thanks a lot Mr John.

The problem was TimestampFormat, i make your suggestion and now it works.

Best Regards.