Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Join us March 10th, 7 ways modern analytics can help you take smarter action. REGISTER NOW
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 II
Specialist II

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;

 

View solution in original post