Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
fredrik_olsson
Contributor III
Contributor III

Combining fields in two tables

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

Order:

IDDateBrand
2342020-01-01Scott
1232020-01-01Scott

 

Marketing:

MarketingDateMarketingAmountMarketingBrand
2020-01-01420 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;

1 Solution

Accepted Solutions
lanlizgu
Creator III
Creator III

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;

 

View solution in original post

2 Replies
JustinDallas
Specialist III
Specialist III

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
Creator III
Creator III

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;