How to calculate difference between timestamps, while fields are in different tables

[#1]

Hi,

I loaded 2 tables within qlikview. I want to add a new field in table "Meldingen", which calculates a time difference. However, one field is present in another table. How can I load the other field, to calculate the time difference.

[Meldingen SMV]:

LOAD

  AutoNumber(JOBCALL_JOB_NR & ' ' & JOBCALL_JOB_DATE) 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];

SQL SELECT *

FROM GTISMVREPL.dbo."JOBCALL_ABNAMRO" WHERE JOBCALL_JOB_DATE > '1-1-2014';

[Meldingen OIB]:

LOAD

  AutoNumber(JOB_NR & ' ' & JOB_DATE) as %Melding.ID,

  AutoNumber(JOB_CONTRACT_NR) as %Locatie.ID,

  AutoNumber(JOB_SAP_REF_NR) as %SAP.ID,

  JOB_NR as [Melding.Nummer],

  JOB_CONTRACT_NR as [Melding.OIB Contractnummer],

  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', 'Openstaand',

  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],

  JOB_CATEGORY_NR as [Melding.Categorie],

  TimeStamp(OIB_UpdateTime, 'DD-MM-YYY hh:mm:ss') as [Melding.Moment bijgewerkt],

  JOB_FEEDBACK as [Melding.Uitgevoerde actie],

  JOB_STAFF_ID as [Melding.Persoon bijgewerkt],

  ContractNaam as [Melding.Contractnaam];

SELECT JOB.*, c.ContractID, ContractNaam, Hoofdindeling, Subindeling, Bijgewerkt, BijwerkerUserID, NLSFB, WPC, Regio

FROM OIB.dbo.JOB

INNER JOIN ContractsMappingSMV m ON JOB_CONTRACT_NR = SMV_CONTRACT_NR AND JOB_DATE > '1-1-2014'

INNER JOIN Contracts c ON c.ContractID = m.ContractID

INNER JOIN [ARN-BBA-S002].GTISMVREPL.dbo.CONTRACT sc ON sc.CONTRACT_NR = m.SMV_CONTRACT_NR AND (REGIO LIKE '%ABN%')

LEFT JOIN WPCs w ON w.ContractID = c.ContractID AND

(JOB_WORKCODE_NR = w.WPC + ' /' + CAST(w.Volgnummer AS VARCHAR(4)) OR JOB_WORKCODE_NR = w.WPC + '/' + CAST(w.Volgnummer AS VARCHAR(4)));

In the table "Meldingen", I want to add this function:

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

Where Melding.Moment verstuurd is from table "Meldingen SMV"

How can I achieve this?

[DavidFoster1]

You are going to have to bring both values together in the same table before doing the interval calculation. I would suggest looking a creating a MAPPING table with one of the time in and using APPLYMAP to bring it into the table based on the common key value. alternatively you could look at a table join, but APPLYMAP tends to be faster.

[#3]

Can you give me an example?

[DavidFoster1]

There is already an Applymap in the statement you supplied. Why do you need an example?

[#5]

I don't fully understand what i should put in the mapping table. For that I was asking an example..

[DavidFoster1 · Accepted solution]

Assuming that JOB_NR is a common key between the 2 tables

MeldingenSMVMap:

MAPPING LOAD

     JOB_NR,

     [Melding.Moment verstuurd]

RESIDENT MeldingenSMV;

ANd then in your load of table

[Meldingen OIB]:

Interval((APPLYMAP('MeldingenSMVMap',JOB_NR,0) - [Melding.Moment gemeld]),'hh')

The 0 being a default value should the APPLYMAP not find a mapping.