topic Re: How to append columns to table? in QlikView

How to append columns to table?

Wed, 09 Jul 2014 10:30:51 GMT

Hi,

I have two tables: Table A and table B.

Table A looks like this:

Alarmcode	Creation Date
12345
99882
55667
99999

Table B looks like t his:

Alarmcode	Call Date	Response Date
12345
99882
55667

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:

[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

Re: How to append columns to table?

ThornOfCrowns — Wed, 09 Jul 2014 10:36:33 GMT

Take a look at the JOIN syntax.

Re: How to append columns to table?

Wed, 09 Jul 2014 10:38:33 GMT

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.

Re: How to append columns to table?

Wed, 09 Jul 2014 10:41:29 GMT

what join you used

Re: How to append columns to table?

avinashelite — Wed, 09 Jul 2014 10:41:38 GMT

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.

Table A:

Load Alarmcode,

Date

Select...

Left join

Table B:

Load Alarmcode,

Call

Select...

Re: How to append columns to table?

Anonymous — Wed, 09 Jul 2014 10:42:07 GMT

Or how about using the ApplyMap() function ?

Re: How to append columns to table?

vikasmahajan — Wed, 09 Jul 2014 10:45:24 GMT

you can join tables with left outer join.

Vikas

Re: How to append columns to table?

PradeepReddy — Wed, 09 Jul 2014 10:51:43 GMT

Try to use left Join..

TableA:

Load *

From TableA;

Left Join(TableA)

Load *

From TableB.;

Re: How to append columns to table?

Wed, 09 Jul 2014 10:53:13 GMT

I already tried LEFT JOIN.

THis is the result:

as you can see there are many duplicates.

Re: How to append columns to table?

PradeepReddy — Wed, 09 Jul 2014 11:07:27 GMT

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.

Regards,

Pradeep

Re: How to append columns to table?

avinashelite — Wed, 09 Jul 2014 11:48:23 GMT

Can you please post your qvw so that we can resolve the issue.

Re: How to append columns to table?

Wed, 09 Jul 2014 13:28:48 GMT

I posted the files

Re: How to append columns to table?

Wed, 09 Jul 2014 13:29:00 GMT

I posted the QVW's

Re: How to append columns to table?

avinashelite — Thu, 10 Jul 2014 04:27:49 GMT

Please load this qvw file , I dont have the permission to reload. I have traced the issue and corrected it.

If u still face issue please let me know.