Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
coloful_architect
Creator II
Creator II

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
Labels (1)
1 Solution

Accepted Solutions
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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,

View solution in original post

2 Replies
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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

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