I have data in two tables that I would like to incorporate into one table.
The first table is a customer order table at individual order level (customers can have multiple orders)with Customer ID and Quantity ordered columns ( as well as a lot of other columns). The second table is a Customer despatches table at individual order despatch level. This has customer ID and and quantity despatched columns ( plus other columns).
I now need to create a new table at customer ID level which will have the columns Customer ID , Quantity Ordered, Quantity Despatched.
Usually it's enough to create a straight table with Customer ID as dimension and two expressions: sum([Quantity ordered]) and sum([Quantity Dispatched]). If you want to calculate it in the script you can do something like this:
Load [Customer ID], sum([Quantity ordered]) as [Quantity Ordered Customer Total]
group by [Customer ID];
Load [Customer ID], sum([Quantity Dispatched]) as [Quantity Dispatched Customer Total]
The join worked perfectly. I have added a bit more to the code to say Total Loaded - Total Delivered.
For some strange reason, the calculation is working fine where there is a value in both columns Total Loaded and Total Delivered. Where there is a value in only one of the columns, it is not performing the calculation. The code now looks like this:
Sum([Bales Loaded])as [Total Loaded]
Resident [BALE LOOP]
group by TIMB;
Sum([Bales Delivered]) as [Total Deliveries]
Group by TIMB;
if([Total Loaded]='',0,[Total Loaded]) as [Total Loaded],
if([Total Deliveries]='',0,[Total Deliveries]) as [Total Deliveries],