Discussion Board for collaboration related to QlikView App Development.
I have two tables: Table A and table B.
Table A looks like this:
Table B looks like t his:
|Alarmcode||Call Date||Response Date|
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:
//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];
//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
Take a look at the JOIN syntax.
I did, Qlikview then appends some rows, which results that Alarmcode has duplicate rows.. That's the reason that I'm asking this question here.
what join you used
Use LEFT JOIN .It will give u the values containing in A along with values which are common in table B, with other columns.
Please refer the attachment.
Or how about using the ApplyMap() function ?
you can join tables with left outer join.
Try to use left Join..
I already tried LEFT JOIN.
THis is the result:
as you can see there are many duplicates.
It seems to be there are multiple enties in the table for Melding.Number.
Try to identify the
1) Common Fields b/w the both tables
2) Unique fields i.e primary keys in both tables.
After Identifying those, Try to use LEFT JOIN, it will work.