Add field to every record in table based on one record in group

[Anonymous]

I have a table similar to below.  I would like to append a field (Master_Product_Id)  into the table on load that looks by Location for a Master_Product = TRUE by Location and adds the Product_Id of the Master_Product to every Product at each Location.  Is ok if it adds it to the Master_Product as well, but that is not how it is displayed below.

Location	Product	Product_Id	Master_Product	Master_Product_Id
Bob	apple	1234a	FALSE	3376t
Bob	orange	2765w	TRUE
Bob	melon	3376t	FALSE	3376t
Jen	paper	3986j	TRUE
Jen	pen	6439h	FALSE	3986j
Jen	pencil	9823g	FALSE	3986j
Jen	cup	7756m	FALSE	9577t
Mary	bike	2213r	FALSE	9577t
Mary	car	9577t	TRUE

[Anonymous]

Help if I made the example correct in the first place...

Location	Product	Product_Id	Master_Product	Master_Product_Id
Bob	apple	1234a	FALSE	2765w
Bob	orange	2765w	TRUE
Bob	melon	3376t	FALSE	2765w
Jen	paper	3986j	TRUE
Jen	pen	6439h	FALSE	3986j
Jen	pencil	9823g	FALSE	3986j
Jen	cup	7756m	FALSE	3986j
Mary	bike	2213r	FALSE	9577t
Mary	car	9577t	TRUE

[Oleg_Troyansky]

If I understand correctly, you want it to happen in the load script, right? Load a temporary table with all the Master products by Location and then join it back to the main table:

temp:

load

     Location,

     Product_Id as Master_Product_Id

resident

     Table

where

     Master_Product = 'TRUE'

;

left join (Table)

load *

resident temp;

drop table temp;

[maxgro · Accepted solution]

a:

load Location, Product, Product_Id, Master_Product inline [

Location, Product, Product_Id, Master_Product, Master_Product_Id

Bob, apple, 1234a, FALSE, 2765w

Bob, orange, 2765w, TRUE,

Bob, melon ,3376t, FALSE, 2765w

Jen, paper, 3986j, TRUE,

Jen, pen, 6439h, FALSE, 3986j

Jen, pencil, 9823g, FALSE, 3986j

Jen, cup, 7756m, FALSE, 3986j

Mary, bike, 2213r, FALSE, 9577t

Mary, car, 9577t, TRUE,

];

left join (a)

load

  Location,

  Product_Id as Master_Product_Id

resident a

Where Master_Product = 'TRUE';

[Anonymous]

Thank you Massimo.

[Anonymous]

Thank you Oleg.