topic Re: How to calculate difference between timestamps? in QlikView

How to calculate difference between timestamps?

Wed, 09 Jul 2014 09:01:41 GMT

Hi,

I have 2 tables with timestamp values:

[Meldingen]:

LOAD

AutoNumber(JOB_NR) as %Melding.ID,

AutoNumber(JOB_CONTRACT_NR) as %Locatie.ID,

AutoNumber(JOB_SAP_REF_NR) as %SAP.ID,

JOB_NR as [Melding.Nummer],

Date(Floor(JOB_DATE)) as [Melding.Datum gemeld],

Time(Frac(JOB_DATE)) as [Melding.Tijd gemeld],

TimeStamp(JOB_DATE, 'DD-MM-YYYY hh:mm:ss') as [Melding.Moment gemeld],

JOB_DESC as [Melding.Omschrijving],

IF( IsNull(JOB_STATUS_NR), 'Onbehandeld',

IF( (JOB_STATUS_NR) = 'O3', 'Open staand',

IF( (JOB_STATUS_NR) = 'O5', 'Afgehandeld',

IF( (OIB_WorkFlowStatus) = 'status0',

'Afgehandeld mail', '<Onbekende status>')))) as [Melding.Status],

ApplyMap('Map_Melding.Prioriteit', JOB_PRIORITY_NR) as [Melding.Prioriteit];

...

// TODO, samenvoegen!

LOAD

AutoNumber(JOBCALL_JOB_NR) as %Melding.ID,

JOBCALL_JOB_NR as [Melding.Nummer SMV],

Date(Floor(JOBCALL_CALL_DATE)) as [Melding.Datum verstuurd],

Time(Frac(JOBCALL_CALL_DATE)) as [Melding.Tijd verstuurd],

Timestamp(JOBCALL_CALL_DATE, 'DD-MM-YYYY hh:mm:ss') as [Melding.Moment verstuurd],

Date(Floor(JOBCALL_REACTION_DATE)) as [Melding.Datum geaccepteerd],

Time(Frac(JOBCALL_REACTION_DATE)) as [Melding.Tijd geaccepteerd],

Timestamp(JOBCALL_REACTION_DATE, 'DD-MM-YYYY hh:mm:ss') as [Melding.Moment geaccepteerd];

...

I want to calculate the difference between the two values colored in blue in hours. As you can see I explicitely converted them to timestamps. When I check at the selection box, I see indeed the format 'DD-MM-YYY'. I did the following in a text object:

=Interval(([Melding.Moment verstuurd] - [Melding.Moment gemeld]),'hh')

However, this doesn't show the value in the text object. What am I doing wrong?

Message was edited by: Nazeem Soeltan Added sample file (with removed sql queries and connction strings)

Re: How to calculate difference between timestamps?

noman212 — Wed, 09 Jul 2014 09:04:45 GMT

Hi Nazeem

Please try this

Load *, if(FieldName, interval(Timestamp(Time,'hh:mm:ss[.fff] TT'))-(Timestamp(925/2400,'h:mm:ss TT'))) as TimeLate;

//LOAD *, If(Num(FieldaAme)>Num(Time#('9:15:00 AM','hh:mm:ss[.fff] TT')),1) as FeieldNAme;

Re: How to calculate difference between timestamps?

Wed, 09 Jul 2014 09:12:11 GMT

I posted this question in the wrong section. I wanted to solve this in the front-end and not in the script. Could you provide me with a solution?

Re: How to calculate difference between timestamps?

noman212 — Wed, 09 Jul 2014 09:23:41 GMT

can u pls upload ur sample file here so can i assist u more eaisly

Re: How to calculate difference between timestamps?

Wed, 09 Jul 2014 09:30:59 GMT

I've added the sample file in the first post

Re: How to calculate difference between timestamps?

jonathandienst — Wed, 09 Jul 2014 09:33:34 GMT

The problem is that there are multiple values of the two fields when the expression is being calculated. To do this in a text box you will need aggregation functions (Min(), Max, Avg()) or a set expression or a manual selection that limits the fields to one unique value each.

Like this:

=Interval(Max([Melding.Moment verstuurd]) - Max([Melding.Moment gemeld])),'hh')

or as an expression in a chart/table where Melding.Nummer and/or [Melding.Nummer SMV] are dimensions.

HTH

Jonathan

Re: How to calculate difference between timestamps?

Wed, 09 Jul 2014 09:36:04 GMT

THank you! And what should I do when I want to calculate the average of that calculated difference?

Re: How to calculate difference between timestamps?

jonathandienst — Thu, 10 Jul 2014 07:20:58 GMT

Depends whether there is one distinct value for [Melding.Nummer] and [Melding.Nummer SMV] for each distinct value of %Melding.ID. If so, then

=Interval(Avg(Aggr([Melding.Moment verstuurd] - [Melding.Moment gemeld], [%Melding.ID.])),'hh')

If there are multiple values, then you need to explain how to get single values for the two fields.

HTH

Jonathan