topic Re: Adding a field from one table to another in QlikView

Adding a field from one table to another

samvile18 — Fri, 25 Jan 2013 12:54:10 GMT

Hello all,

Can someone help me please?

I need to take the field ParentAdvisor from the table SD and add it to the table TM where the SchemeId is the same.

I'm a little stuck with the syntax though,

All help is appreciated

Re: Adding a field from one table to another

Fri, 25 Jan 2013 14:46:02 GMT

Hi,

could you post structure of these two tables, with some sample data?

regards

Re: Adding a field from one table to another

Fri, 25 Jan 2013 14:53:22 GMT

Hi SamVile

You can try this:

Table1:

Load

SchemeId

,ParentAdvisor

From SD;

Table2:

Load

SchemeId

(Rest of your fields)...

From TM

Join

Load

ParentAdvisor

Resident Table1;

I hope to make myself clear with this, perhaps if you want you can post your tables and I will glad to help you with a .QVW example.

Regards

Re: Adding a field from one table to another

samvile18 — Fri, 25 Jan 2013 16:00:03 GMT

I can't get that to work for some reason?

Here's the script I have for the tables

SD:

LOAD SchemeId,

[Parent Advisor]

FROM

[\\wscqvw03\Qlikview\Source Documents\Source Data\QVDs\SchemeDimensions.qvd]

(qvd);

TM:

LOAD SchemeId,

ProcessName,

ProcessType,

FROM

[\\wscqvw03\Qlikview\Source Documents\Source Data\QVDs\SLAData.qvd]

(qvd)

What I want to do now is take the Parent Advisor field into the TM table where the SchemeId is the same.

Re: Adding a field from one table to another

Fri, 25 Jan 2013 16:05:18 GMT

Ok, you can try this:

SD:

LOAD SchemeId,

[Parent Advisor]

FROM

[\\wscqvw03\Qlikview\Source Documents\Source Data\QVDs\SchemeDimensions.qvd]

(qvd);

TM:

LOAD SchemeId,

ProcessName,

ProcessType,

FROM

[\\wscqvw03\Qlikview\Source Documents\Source Data\QVDs\SLAData.qvd]

(qvd);

Join

Load

[ParentAdvisor]

Resident

SD;

QlikView joins the table by the common field in automatic.

I hope this help you.

Regards

Re: Adding a field from one table to another

wizardo — Fri, 25 Jan 2013 23:27:43 GMT

HI THERE

carlosmartinez's answer is partialy correct ,

if i understand what you asking then you hould use left join on the last load, like this

SD:

LOAD SchemeId,

[Parent Advisor]

FROM

[\\wscqvw03\Qlikview\Source Documents\Source Data\QVDs\SchemeDimensions.qvd]

(qvd);

TM:

LOAD SchemeId,

ProcessName,

ProcessType,

FROM

[\\wscqvw03\Qlikview\Source Documents\Source Data\QVDs\SLAData.qvd]

(qvd)

LEFT JOIN

LOAD SchemeId,

[Parent Advisor]

resident SD;

another alternative is using mapping and apply map like this:

SD_MAP:

mapping LOAD SchemeId,

[Parent Advisor]

FROM

[\\wscqvw03\Qlikview\Source Documents\Source Data\QVDs\SchemeDimensions.qvd]

(qvd);

TM:

LOAD

SchemeId,

applymap('SD_MAP, SchemeId) as [Parent Advisor],

ProcessName,

ProcessType,

FROM

[\\wscqvw03\Qlikview\Source Documents\Source Data\QVDs\SLAData.qvd]

(qvd)

and then load the SD table again if you need it

if you dont need the SD table at all to be left in the datamodel then a simple left JOIN between them is enough

like this

TM:

LOAD SchemeId,

ProcessName,

ProcessType,

FROM

[\\wscqvw03\Qlikview\Source Documents\Source Data\QVDs\SLAData.qvd]

(qvd)

LEFT JOIN

//SD:

LOAD SchemeId,

[Parent Advisor]

FROM

[\\wscqvw03\Qlikview\Source Documents\Source Data\QVDs\SchemeDimensions.qvd]

(qvd);

as you see there are many options,

i guess the right one for you depends on which tables you need left in the datamodel and how many fields are in them

hope this helps

Mansyno