7 Replies Latest reply: Jan 12, 2016 3:01 AM by Tamil arasu

# How to get the result shown in the picture?

Can anyone help me build this load ?

• ###### Re: How to get the result shown in the picture?

Can you provide the Excel file to load the data into the application?

• ###### Re: How to get the result shown in the picture?

Maybe something like this:

Orders:

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

• ###### Re: How to get the result shown in the picture?

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

left Join

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,

• ###### Re: How to get the result shown in the picture?

Hi,

```Orders Table:
Orders table.

Left join(Orders Table):
Freight table.

```

Else provide some data!

-Hirish

• ###### Re: How to get the result shown in the picture?

Hi,

Try like this

Orders:

AutoNumber(Customer & Month & Year) AS Key,

*

FROM Orders;

Freight:

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.

• ###### Re: How to get the result shown in the picture?

Hi,

Please find the attached qvw file.

• ###### Re: How to get the result shown in the picture?

Check the below script,

```Test:
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
Sum(Weight) as [Total Weight]
Resident Test Group by %Key;

NoConcatenate
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.