How to calculate difference between timestamps?

[#1]

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)

[noman212]

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;

[#3]

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?

[noman212]

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

[#5]

I've added the sample file in the first post

[jonathandienst]

Hi

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

[#7]

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

[jonathandienst]

Hi

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