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?
