Skip to main content
Announcements
Accelerate Your Success: Fuel your data and AI journey with the right services, delivered by our experts. Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
aviadbein
Contributor II
Contributor II

Join/Merge/Concatenate two tables

Hi all,

I've got two tables, "Parent Customer and "Child Customer", like this:

Parent Customer:

CustomerRevenue
Customer A100
Customer B50
Customer C20
Customer D10

Child Customer:

CustomerCustomerChildChildShare
Customer ACustomer A10.2
Customer ACustomer A20.3
Customer ACustomer A30.5
Customer BCustomer B10.9
Customer BCustomer B20.1

and I want to merge them like this:

CustomerRevenue
Customer A1100*0.2
Customer A2100*0.3
Customer A3100*0.5
Customer B150*0.9
Customer B250*0.1
Customer C20
Customer D10

any ideas?

Thanks in advance,

Aviad

1 Solution

Accepted Solutions
sunny_talwar

Try this:

Parent:

LOAD * INLINE [

    Customer, Revenue

    Customer A, 100

    Customer B, 50

    Customer C, 20

    Customer D, 10

];

Left Join (Parent)

LOAD * INLINE [

    Customer, CustomerChild, ChildShare

    Customer A, Customer A1, 0.2

    Customer A, Customer A2, 0.3

    Customer A, Customer A3, 0.5

    Customer B, Customer B1, 0.9

    Customer B, Customer B2, 0.1

];

FinalTable:

LOAD If(Len(CustomerChild) = 0, Customer, CustomerChild) as Customer,

  If(Len(CustomerChild) = 0, Revenue, Revenue * ChildShare) as Revenue

Resident Parent;

DROP Table Parent;

View solution in original post

3 Replies
sunny_talwar

Try this:

Parent:

LOAD * INLINE [

    Customer, Revenue

    Customer A, 100

    Customer B, 50

    Customer C, 20

    Customer D, 10

];

Left Join (Parent)

LOAD * INLINE [

    Customer, CustomerChild, ChildShare

    Customer A, Customer A1, 0.2

    Customer A, Customer A2, 0.3

    Customer A, Customer A3, 0.5

    Customer B, Customer B1, 0.9

    Customer B, Customer B2, 0.1

];

FinalTable:

LOAD If(Len(CustomerChild) = 0, Customer, CustomerChild) as Customer,

  If(Len(CustomerChild) = 0, Revenue, Revenue * ChildShare) as Revenue

Resident Parent;

DROP Table Parent;

aviadbein
Contributor II
Contributor II
Author

Hi Sunny, Thanks for the quick reply. another question (and more complex):

What can I do if field "Revenue" is in third Table (Fact Table)?

sunny_talwar

May be join or use ApplyMap... Won't be able to suggest more without looking