If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.
I'm trying to combine / divide fields in two different tables in the load script.
Order:LOADIDDate,Brand,Date & '|' & Brand as CompositeKeyOrderDateAndBrand;
Order_Count:LOADCompositeKeyOrderDateAndBrand,Count(ID) as NumberOfOrdersRESIDENT OrderGROUP 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;
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, Brand234, 2020-01-01, Scott123, 2020-01-01, Scott];
Marketing:LOAD * INLINE [MarketingDate, MarketingAmount, MarketingBrand2020-01-01, 420, Scott];
Order1:LOADID,Date,Brand,Date & '|' & Brand as CompositeKeyOrderDateAndBrandresident Order;
drop table Order;
Order_Count:LOADCompositeKeyOrderDateAndBrand,Count(ID) as NumberOfOrdersRESIDENT Order1GROUP BY CompositeKeyOrderDateAndBrand;
Marketing1:LOADMarketingDate,MarketingBrand,MarketingAmount,MarketingDate & '|' &MarketingBrand as CompositeKeyOrderDateAndBrandresident Marketing;
drop table Marketing;
LEFT JOIN (Marketing1)LOADCompositeKeyOrderDateAndBrand,NumberOfOrdersRESIDENT Order_Count;
Marketing2:loadCompositeKeyOrderDateAndBrand,MarketingAmount / NumberOfOrders as MarketingPerOrderCostresident Marketing1;
drop table Marketing1;
View solution in original post
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.