topic hi... in QlikView

hi...

Wed, 28 Mar 2012 03:11:56 GMT

How can i apply outer join between two table when i have more than 3 field common:

Table1

Recruitment:

LOAD [Organization Name],

[Employee No],

[Full Name],

[Date First Hired],

[Date Join],

FY,

[Date Resign],

Table2

[Employee No],

FY,

[Organization Name],

[Full Name],

[Group],

Status,

[Date Join],

[Date Resign],

Days,

Years,

when i am doing join i am getting repeated data what should i do..??

hi...

Wed, 28 Mar 2012 05:01:03 GMT

Hi ,

For in this case use Employee No as Primary key to link the two tables.

After that you can alias the other fields.

For example in the second table

Full Name as Full Name1

FY as FY1

and [Organization Name] as [Organization Name1].

When you are making the join you are getting the repeted data because in your case after linking synthetic keys are created.

So try to avoid synthetic keys.

regards

Kamal

hi...

jagan — Wed, 28 Mar 2012 05:09:17 GMT

Hi,

Qlikview automatically joins the tables even if there are multiple fields with the same Field name. If there is one common field then the join based on that one field, if there are more than one common field then the join would based on multiple fields.

Try the below script

RecruitmentTemp:

LOAD [Organization Name],

[Employee No],

[Full Name],

[Date First Hired],

[Date Join],

FY,

[Date Resign]

FROM DataSource1;

OUTER JOIN

LOAD

[Employee No],

FY,

[Organization Name],

[Full Name],

[Group],

Status,

[Date Join],

[Date Resign],

Days,

Years

FROM DataSource2;

By using the above you won't get any synthetic keys.

If you are getting Duplicate values then try using using this script

Recruitment:

LOAD DISTINCT

RESIDENT RecruitmentTemp;

DROP TABLE RecruitmentTemp;

This will remove the duplicate rows.

Hope this helps you.

Regards,

Jagan.

hi...

Wed, 28 Mar 2012 05:22:38 GMT

hi,

i think u need to qualify your fields and then map only those field which you want to join...

suppose you want to apply join on Employee NO

Qualify *;

Unqualify [Employee No];

Table1

Recruitment:

LOAD [Organization Name],

[Employee No],

[Full Name],

[Date First Hired],

[Date Join],

FY,

[Date Resign],

Qualify *;

Unqualify [Employee No];

Table2

[Employee No],

FY,

[Organization Name],

[Full Name],

[Group],

Status,

[Date Join],

[Date Resign],

Days,

Years,

now only Employee no will be mapped

hi...

Wed, 28 Mar 2012 06:25:19 GMT

Thanks to all

hi...

Wed, 28 Mar 2012 06:26:09 GMT

exactly i did this bt still i am getting duplicate value aftr usin distinct i dont knw why??

hi...

jagan — Wed, 28 Mar 2012 06:52:06 GMT

Hi,

Can you attach the sample file which you are working.

Regards,

Jagan.