Join/Merge/Concatenate two tables

[aviadbein]

Hi all,

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

Parent Customer:

Customer	Revenue
Customer A	100
Customer B	50
Customer C	20
Customer D	10

Child Customer:

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

and I want to merge them like this:

Customer	Revenue
Customer A1	100*0.2
Customer A2	100*0.3
Customer A3	100*0.5
Customer B1	50*0.9
Customer B2	50*0.1
Customer C	20
Customer D	10

any ideas?

Thanks in advance,

Aviad

[sunny_talwar · Accepted solution]

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]

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