
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
better way to create a new field from a left joined table
I want to link two tables together by left join , to put another way, stack/append one table( table B in my case) on table A to have table C. For a purpose to link some other tables, I need to have new filed/key "new dim" by combing two fields from table A and B. ( like table C)
so what I did was : (conceptual process below, dont mind syntax error)
load * A from table A ;
left join load * from table B;
left join load product & address as new_dim from table A , resident table A;
Somehow, this causes me a infinite loop seems to take forever.
now I end up creating a left join table without creating a combined "new _dim" field and then convert into a QVD file.
From that QVD file, I then load it and create a combined "new_dim".
Feel like this is not efficient at all, wondering if there is a smarter way to have this table C with this combined filed more efficiently.
Table A |
Product |
Account |
Measurement fields |
Table B |
Account |
Address |
Left Joined Table C |
Product |
Account |
Address |
Measurement fields |
Product & address as new dim |
Accepted Solutions


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi there!
So, the problem with the "infinite loop" is caused by the fact that you are joining tables with no common keys, and that is causing a cartesian multiplication, which could take forever for large tables.
You have two ways of solving this one properly:
1. Using Left Join and a Resident reload:
Load table A, then left-join table B. Then reload the resulting table using a Resident load and calculate the combined key. Drop the intermediate table after that.
2. Using Mapping (my personal preference:
- Load table B into a map, using a MAPPING load
- load table A. In the process of loading, add the new field Address with the ApplyMap() function, using the map that you just created before. Something like this (conceptually):
Address_Map:
MAPPING LOAD
Account,
Address
FROM table B
;
TableA:
// Preceding load to use the new field Address:
LOAD
*,
Product & '|' & Address as Combo_Key
;
LOAD
*,
ApplyMap('Address_Map", Account) as Address
FROM
table A
----------------
Cheers,


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi there!
So, the problem with the "infinite loop" is caused by the fact that you are joining tables with no common keys, and that is causing a cartesian multiplication, which could take forever for large tables.
You have two ways of solving this one properly:
1. Using Left Join and a Resident reload:
Load table A, then left-join table B. Then reload the resulting table using a Resident load and calculate the combined key. Drop the intermediate table after that.
2. Using Mapping (my personal preference:
- Load table B into a map, using a MAPPING load
- load table A. In the process of loading, add the new field Address with the ApplyMap() function, using the map that you just created before. Something like this (conceptually):
Address_Map:
MAPPING LOAD
Account,
Address
FROM table B
;
TableA:
// Preceding load to use the new field Address:
LOAD
*,
Product & '|' & Address as Combo_Key
;
LOAD
*,
ApplyMap('Address_Map", Account) as Address
FROM
table A
----------------
Cheers,

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
yeah..right after I made this post, it occurred to me maybe I can try by dropping the table.
but your second method is the money. Glad I asked.
Thanks
