Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have the following data model.
I'd like to join table 1 and table 2, so that my final table is table 1 with an extra attribute which is the 2_volume field from table 2, and joined on the 1_venue and 2_venue. What's the easier way to do this in the load script?
Solution 2
bridge:
load 1_id,2_id
From bridgeSource;
left join(bridge)
2_id,2_volume,2_venue
From table2Source;
table1:
Load
1_id,1_volume,1_venue
From table1Source;
Left Join(table1)
Load Distinct id_1,2_volume,2_venue Resident bridge;
drop table bridge;
exit Script;
What is the cardinality between Table1 and bridge and bridge and Table 2 ??
Hi @johnnyjohn,
Do I understand clear that 1_venue and 2_venue are the key fields that can be joined?
If so, then you can left join [table 2] to [table 1] by renaming 2_venue as 1_venue.
LEFT JOIN ([table 1])
LOAD
2_venue AS 1_venue
,2_volume
FROM ......
;
Table 1 -> Bridge : One to Many
Bridge -> Table 2 : Many to One
Thanks, I'm not sure this takes into account the bridge table dependency though - Table 1 and Table 2 are linked via the bridge table, and only when those are linked, should the mapping be done
Solution 1
bridge:
load 1_id,2_id
From bridgeSource;
left join(bridge)
2_id,2_volume,2_venue
From table2Source;
MapVol:
Mapping Load 1_id,2_volume Resident bridge;
MapVenue:
Mapping Load 1_id,2_venue Resident bridge;
Drop table bridge;
table1:
Load
1_id,1_volume,1_venue
,Applymap('MapVol',1_id) as 2_volume
,Applymap('MapVenue',1_id) as 2_venue
From table1Source;
exit Script;
Solution 2
bridge:
load 1_id,2_id
From bridgeSource;
left join(bridge)
2_id,2_volume,2_venue
From table2Source;
table1:
Load
1_id,1_volume,1_venue
From table1Source;
Left Join(table1)
Load Distinct id_1,2_volume,2_venue Resident bridge;
drop table bridge;
exit Script;
Thank you, that seems to be creating synthetic keys and a loop however. Does the second solution include the joining using the venue ?
how is this creating a synthetic key ???
can you post a snapshot of your model or provide some sample data?
All good sir, had to change some of the code around a little. Thanks