topic Re: How to calculate difference between timestamps, while fields are in different tables in QlikView

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

Wed, 13 Aug 2014 07:22:44 GMT

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?

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

DavidFoster1 — Wed, 13 Aug 2014 07:32:28 GMT

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.

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

Wed, 13 Aug 2014 08:09:16 GMT

Can you give me an example?

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

DavidFoster1 — Wed, 13 Aug 2014 08:17:10 GMT

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

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

Wed, 13 Aug 2014 08:23:05 GMT

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

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

DavidFoster1 — Wed, 13 Aug 2014 08:41:01 GMT

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.