topic Re: Set Analysis Question in QlikView

Set Analysis Question

mphekin12 — Wed, 12 Mar 2014 17:56:39 GMT

Hello,

I have a question regarding set analysis. I have a Products, a Work Order and a Component table. The Products table contains information for all of our products. The Work Order table contains sales stats. Sometimes we build products by combining several products together. The information of which products make up our built products are contained in the Components table.

Product Table

ProductID

ProductDescription

Work Order

Product ID

OrderQty

Cost

Component

ProductID

ComponentID

I need to create a chart that will show me the Order Qty for a given Product as well as the components of the Product.

I have attached a screenshot of what the chart should look like. I'm thinking that the Set Analysis logic would be something like

sum({1<ProductID={ComponentID}>} OrderQty) for all of the components, but I can't get the formual to work.

Any help would be greatly appreciated!

Re: Set Analysis Question

Wed, 12 Mar 2014 18:00:41 GMT

Please provide some sample data!

Vinay

Re: Set Analysis Question

Wed, 12 Mar 2014 18:05:41 GMT

HI,

Not sure why you need set analysis here. Just sum(Order Qty) should work. Is there any reason why you want to use Set analysis?

Re: Set Analysis Question

mphekin12 — Wed, 12 Mar 2014 18:10:50 GMT

If I use sum(Order Qty) it only gives me the sum of the Order Qty for the Parent Product ID. I need this sum, as well as the sum for all of the components.

Re: Set Analysis Question

mphekin12 — Wed, 12 Mar 2014 18:28:31 GMT

Here is a small sample. I hope this explains my situation a little better.

Any help would be greatly appreciated!

Re: Set Analysis Question

srchilukoori — Wed, 12 Mar 2014 18:46:29 GMT

The order Qty data is associated with the Product ID in the Test.qvw

When you use Sum(OrderQty) you are in fact aggregating the data at the Product ID level.

Re: Set Analysis Question

mphekin12 — Wed, 12 Mar 2014 18:52:32 GMT

srchilukoori,

Thanks for your reply. Maybe I'm not explaining the problem correctly. If you open the sample application and select ProductID MFLIDPNL17X1012GAHRF&F you will see that there are 3 components to that Product. What I need is the Usage for that Product as well as the Usage for the 3 components. In the Pivot table in the sample app, the Usage displayed is only for the Parent Product ID.

The sample size I provided was very small, so it would be easy to figure out, but when you have well over a million products it becomes more challenging.

Thank you!

Re: Re: Set Analysis Question

srchilukoori — Wed, 12 Mar 2014 19:07:17 GMT

use this expression:

=Sum(Total <ProductID> OrderQty)

Re: Set Analysis Question

mphekin12 — Wed, 12 Mar 2014 19:12:48 GMT

srchilukoori,

That formula doesn't work. I opened up the Test.qvw and it looks like you changed the data model, which isn't going to work in my case.

Thanks for all of your help!

Re: Set Analysis Question

srchilukoori — Wed, 12 Mar 2014 19:22:30 GMT

In your sample data,

Products:

LOAD ProductID,

[Product Description]

FROM

Book1.xlsx

(ooxml, embedded labels, table is Product);

WorkOrder:

LOAD ProductID,

OrderQty, ========> It doesn't link Component ID with Order Qty (Results in cartesian product)

Cost

FROM

Book1.xlsx

(ooxml, embedded labels, table is WO);

Components:

LOAD ProductID,

ComponentID

FROM

Book1.xlsx

(ooxml, embedded labels, table is Component);

--------------------------------------------------------------------------------------------------------------------------------------------------------------

The model I used links Order Qty with Product ID and Component ID, which is necessary to show the data at the product ID and Component ID levels.

Re: Set Analysis Question

srchilukoori — Wed, 12 Mar 2014 19:23:42 GMT

However, the expression should still work.

Re: Set Analysis Question

Wed, 12 Mar 2014 19:24:12 GMT

Can you rephrase : Usage for that Product as well as the Usage for the 3 components

When i added Product ID to that table i got the above result.

Which means per your data model this product has three different components but just one OrderQty.

Can you post an example of how you want the data to look like(in the resultant table)?

Re: Set Analysis Question

javier_florian — Wed, 12 Mar 2014 19:29:27 GMT

Do you try with aggr(Sum(Qty), Dim1)?

-JFlorian

Re: Re: Set Analysis Question

mphekin12 — Wed, 12 Mar 2014 19:48:58 GMT

Ajay,

I have attached another sample application. If you look on the left hand side, it will show you what results I would like to see.

The table you posted shows the Usage for just the Product ID. That product has three components. Each of these components are in the Products and have records in the Work Orders table. I need to see the Usage for these components.

Again, look at the attached application and see if it makes sense now.

Thank you!

Re: Re: Re: Set Analysis Question

Wed, 12 Mar 2014 20:11:13 GMT

Now I fully understand. Attached with answer. Check the back-end, made a small change.

Let me know if you run into any issues.

Thanks

Re: Set Analysis Question

mphekin12 — Wed, 12 Mar 2014 20:22:27 GMT

Ajay,

Thanks for you help! Unfortunately I cannot change the data model.

Re: Set Analysis Question

mphekin12 — Wed, 12 Mar 2014 20:26:20 GMT

I want to thank everyone for their help!

After some digging around the message board and the QlikView for Developers Cookbook I was able to come up with a solution using Set Analysis. I am posting the final application for anyone interested in learning how I solved this problem.