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

Wed, 13 Aug 2014 07:21:24 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

Anonymous — Wed, 13 Aug 2014 08:45:46 GMT

Hi,

If %Melding.ID is Primary key and the link between both the tables , then you can create a mapping table separate as below :

Maptable:

Mapping load

%Melding.ID,

[Melding.Moment verstuurd]

resident Meldingen SMV ;

and then use applymap as below to calculate difference :

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

Regards,

Jatin Kolhe.

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

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