Both the tables have the field Alarmcode in common.
Table B has Alarmcode (values) which are present in Table A.
I want the columns Call Date and Response Date to be added into Table A.
It is possible that Table A has Alarmcodes which are not present in Table B. In that case these rows will have an empty value at Call Date and Response Date.
This is the code:
This is the code:
[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', '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];
...
LEFT JOIN([Meldingen])
LOAD
//AutoNumber(JOBCALL_JOB_NR) as %Melding.ID,
JOBCALL_JOB_NR as [Melding.Nummer],
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];
...
How can I achieve this?
Message was edited by: Nazeem Soeltan Added code from script.
Message was edited by: Nazeem Soeltan I've added 2 QVS's. the one with the left join and the other without. The field JOB_NR in the first table, is the same as JOBCALL_JOB_NR in the second table.
Message was edited by: Nazeem Soeltan
In the files, The look at the tab: MELDINGEN.
The first table can be seen as table A and the second, table B
