Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
First of all, you shouldn't see this result since you are filtering on admin ='s'
Then, duplicating lines may happen when JOINing, hence
Sorry, the condition is admin = 'Y'
let me check this link
Thank you
maiby
Left Join (table1)
Load distinct
id,
idmin
Resident table2;
or
Left Join (table1)
Load
id,
state, client,
admin
Resident table2
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.
Make sure that you are joining these two tables on correct column that's common between two.
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.