Left Join Duplicated Rows

[vdcastro]

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

[swuehl]

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

[vdcastro]

Sorry, the condition is admin = 'Y'

let me check this link

Thank you

[florentina_doga]

maiby

Left Join (table1)

Load distinct

  id,

  idmin

Resident table2;

or

Left Join (table1)

Load

  id,

state, client,

  admin

Resident table2

[manishnarang]

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.

[pooja_sn]

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

[MindaugasBacius]

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.