Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to get the result shown in the picture?

Can anyone help me build this load ?


problem.png

7 Replies
sunny_talwar

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

marcus_sommer

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

PrashantSangle

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,

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
HirisH_V7
Master
Master

Hi,

Orders Table:

Load*,

Orders table.

Left join(Orders Table):

Load*,

Freight table.



Else provide some data!


-Hirish

HirisH
“Aspire to Inspire before we Expire!”
jagan
Luminary Alumni
Luminary Alumni

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.

Anonymous
Not applicable
Author

Hi,

Please find the attached qvw file.

Hope this satisfies your requirement.

tamilarasu
Champion
Champion

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.