Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

How to get the result shown in the picture?

Can anyone help me build this load ?


problem.png

7 Replies

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:

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

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

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,

Regards,
Prashant Sangle
hirishv7
Honored Contributor

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

Hi,

Orders Table:

Load*,

Orders table.

Left join(Orders Table):

Load*,

Freight table.



Else provide some data!


-Hirish

MVP
MVP

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

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.

leni_balakrishn
Contributor III

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

Hi,

Please find the attached qvw file.

Hope this satisfies your requirement.

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

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;

Capture.PNG

Always try to provide some sample data when posting question like this.

Community Browser