better way to create a new field from a left joined table

[coloful_architect]

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

[Oleg_Troyansky · Accepted solution]

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,

[coloful_architect]

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