Combining fields in two tables

[fredrik_olsson]

I'm trying to combine / divide fields in two different tables in the load script.

Order:

ID	Date	Brand
234	2020-01-01	Scott
123	2020-01-01	Scott

Marketing:

MarketingDate	MarketingAmount	MarketingBrand
2020-01-01	420	Scott

Order:
LOAD
ID
Date,
Brand,
Date & '|' & Brand as CompositeKeyOrderDateAndBrand;

Order_Count:
LOAD
CompositeKeyOrderDateAndBrand,
Count(ID) as NumberOfOrders
RESIDENT Order
GROUP BY CompositeKeyOrderDateAndBrand;

Marketing:
LOAD MarketingDate,
MarketingBrand,
MarketingAmount,
MarketingDate & '|' &MarketingBrand as CompositeKeyOrderDateAndBrand;

Here I want to create the marketing table dividing MarketingAmount with  NumberOfOrders:

LEFT JOIN (Marketing)
LOAD CompositeKeyOrderDateAndBrand,
MarketingAmount / NumberOfOrders as MarketingPerOrderCost <-- here it fails....
RESIDENT Order_Count;

[JustinDallas]

It's failing?  What error is it giving you?

Are you sure your numerator and denominator in your division are both numbers?  You can use IsNull and IsNum to check your dataset.

[lanlizgu · Accepted solution]

It is failing because you are trying to obtain the MarketingAmount  from the Order_Count table when this field is not at that table.

You should first do a left join adding the NumberOfOrders from Order_Count and later calculate the field (once the table has two fields).

Please find below a script sample:

Order:
LOAD * INLINE [
ID, Date, Brand
234, 2020-01-01, Scott
123, 2020-01-01, Scott
];

Marketing:
LOAD * INLINE [
MarketingDate, MarketingAmount, MarketingBrand
2020-01-01, 420, Scott
];

Order1:
LOAD
ID,
Date,
Brand,
Date & '|' & Brand as CompositeKeyOrderDateAndBrand
resident Order;

drop table Order;

Order_Count:
LOAD
CompositeKeyOrderDateAndBrand,
Count(ID) as NumberOfOrders
RESIDENT Order1
GROUP BY CompositeKeyOrderDateAndBrand;

Marketing1:
LOAD
MarketingDate,
MarketingBrand,
MarketingAmount,
MarketingDate & '|' &MarketingBrand as CompositeKeyOrderDateAndBrand
resident Marketing;

drop table Marketing;

LEFT JOIN (Marketing1)
LOAD
CompositeKeyOrderDateAndBrand,
NumberOfOrders
RESIDENT Order_Count;

Marketing2:
load
CompositeKeyOrderDateAndBrand,
MarketingAmount / NumberOfOrders as MarketingPerOrderCost
resident Marketing1;

drop table Marketing1;