Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Can anyone help me build this load ?
Can you provide the Excel file to load the data into the application?
Maybe something like this:
Orders:
Load * From OrderTable;
Left join
Load [Customer ID], Month, Year, sum(Weight) as [Total Weight / Customer] Resident Orders;
Left join
Load Customer as [Customer ID], Month, Year, Freight From FreightTable;
[Orders + Freight]:
Load *, Freight / [Total Weight / Customer] * Weight as [Freight Proportion] Resident Orders
- Marcus
Hi,
what is the formula for Freight Proportion??
Since for other field than Freight Proportion
can try left Join in script to combine both table
try like
Load * from Orders;
left Join
Load * from Freight;
then
Create straight table -> take
dimension
CustomerID,
OrderId,
Month,
Year,
ProductID
Expression:
Sum(Weight) for Weight
Sum(Total<CustomerID,Month,Year> Weight) for Total Weight / Customer
Regards,
Hi,
Orders Table:
Load*,
Orders table.
Left join(Orders Table):
Load*,
Freight table.
Else provide some data!
-Hirish
Hi,
Try like this
Orders:
LOAD
AutoNumber(Customer & Month & Year) AS Key,
*
FROM Orders;
Freight:
LOAD
AutoNumber(Customer & Month & Year) AS Key,
Freight
FROM Freight;
Now in chart try like this
Chart:
Dimension: CustomerID, OrderID, Month, Year, Product ID
Expressions:
Weight : Sum(Weight)
Total Weight / Customer : Sum(TOTAL <CustomerID> Weight)
Freight Proportional: (Sum(Freight)/ Sum(TOTAL <CustomerID> Weight)) * Sum(Weight)
Hope this helps you.
Regards,
jagan.
Hi,
Please find the attached qvw file.
Hope this satisfies your requirement.
Check the below script,
Test:
LOAD *,
CustId & Month & Year as %Key
FROM
Book1.xlsx
(ooxml, embedded labels, table is Sheet1);
Left Join
LOAD Customer & Month & Year as %Key,
Freight
FROM
[Book1 - Copy.xlsx]
(ooxml, embedded labels, table is Sheet1);
Left Join
Load %Key,
Sum(Weight) as [Total Weight]
Resident Test Group by %Key;
NoConcatenate
LOAD CustId,
OrderId,
Month,
Year,
ProductId,
Weight,
Weight/[Total Weight] as Weight%,
Freight * Weight/[Total Weight] as FreightProportional
Resident Test;
Drop Table Test;
Always try to provide some sample data when posting question like this.