Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
vdcastro
Partner - Contributor III
Partner - Contributor III

Left Join Duplicated Rows

Hello everyone,

i am having a problem with left join

i have these tables:

table 1

idstateclient
1416

table 2

idstateclientadmin
1216Y
1317Y
1417Y

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:

idstateclientadmin
1416Y
1417Y

and i need:

idstateclientadmin
1416Y

Can someone help me?

Greetings

Vítor

6 Replies
swuehl
MVP
MVP

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
Partner - Contributor III
Partner - Contributor III
Author

Sorry, the condition is admin = 'Y'

let me check this link

Thank you

florentina_doga
Partner - Creator III
Partner - Creator III

maiby

Left Join (table1)

Load distinct

  id,

  idmin

Resident table2;

or

Left Join (table1)

Load

  id,

state, client,

  admin

Resident table2

manishnarang
Partner - Creator
Partner - Creator

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
Creator
Creator

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

MindaugasBacius
Partner - Specialist III
Partner - Specialist III

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.