topic Re: Left Join Duplicated Rows in QlikView

Left Join Duplicated Rows

vdcastro — Wed, 13 Jul 2016 16:53:19 GMT

Hello everyone,

i am having a problem with left join

i have these tables:

table 1

id	state	client
1	4	16

table 2

id	state	client	admin
1	2	16	Y
1	3	17	Y
1	4	17	Y

and i want put that field admin on table 1

so i am doing:

Left Join (table1)

Load

id,

idmin

Resident table2

where admin='S';

and the result is:

id	state	client	admin
1	4	16	Y
1	4	17	Y

and i need:

id	state	client	admin
1	4	16	Y

Can someone help me?

Greetings

Vítor

Re: Left Join Duplicated Rows

swuehl — Wed, 13 Jul 2016 17:14:29 GMT

First of all, you shouldn't see this result since you are filtering on admin ='s'

Then, duplicating lines may happen when JOINing, hence

Don't join - use Applymap instead

Re: Left Join Duplicated Rows

vdcastro — Thu, 14 Jul 2016 08:02:46 GMT

Sorry, the condition is admin = 'Y'

let me check this link

Thank you

Re: Left Join Duplicated Rows

florentina_doga — Thu, 14 Jul 2016 09:13:59 GMT

maiby

Left Join (table1)

Load distinct

id,

idmin

Resident table2;

Left Join (table1)

Load

id,

state, client,

admin

Resident table2

Re: Left Join Duplicated Rows

manishnarang — Thu, 14 Jul 2016 09:22:03 GMT

I am not using qlikview script editor.. so if there is any small syntax missing correct that.. but the below should work:

MapIDs:

Mapping Load id & '|' & state & '|' & client as MapID,

admin as MapValue

resident table2;

Table1WhereAdmintobeAdded:

Load Applymap ('MapIDs', id & '|' & state & '|' & client , null()) as admin,

id,

state,

client

resident table1;

I am considering that you already have loaded table1 and table2 earlier. use concatenate or noconcatenate according to when you are loading these tables etc.

Re: Left Join Duplicated Rows

pooja_sn — Thu, 14 Jul 2016 09:36:30 GMT

Make sure that you are joining these two tables on correct column that's common between two.

Re: Left Join Duplicated Rows

MindaugasBacius — Thu, 14 Jul 2016 09:41:57 GMT

map_admin:
mapping Load distinct
  id,
  idmin
Resident table2;

tmp:
Load *
     ,Applymap('map_admin', id, ':') as admin
Resident Table1;

Drop table Table1;
Rename table tmp to Table1;

If there are only two columns in your table then suggest you to use ApplyMap statement.