topic Re: Problem with calculations of costs and sells in QlikView

Problem with calculations of costs and sells

slribeiro — Wed, 25 Nov 2020 16:16:04 GMT

Greetings, i wonder if you could help me solve my problem here...

I have a pivot table where i have the following result:

What i would like to have it should be something like this...

Basically, i'm having problems on the association of the values by the name because the values aren't directly associated in the model.

Hope you can help me

Best regards, Simão Ribeiro

Re: Problem with calculations of costs and sells

tyagishaila — Tue, 29 Mar 2016 11:01:50 GMT

share sample data.

are component1....... component6 values present in both dim1 and dim2?

Re: Problem with calculations of costs and sells

robert_mika — Tue, 29 Mar 2016 11:03:15 GMT

How does your model look like?

Re: Problem with calculations of costs and sells

Kushal_Chawda — Tue, 29 Mar 2016 11:05:25 GMT

I think your data is not consistent. May be you need to change your data model. Dim2 dimesion is not associated with cost value.

Re: Problem with calculations of costs and sells

avinashelite — Tue, 29 Mar 2016 11:07:31 GMT

Try like this

Temp:

Mapping LOAD

dim1,

costvalue

from table where

dim1 like 'component*';

Result:

LOAD dim1,

dim2,

Applymap('Temp',dim2) as costvalue,

sellvalue;

from table

dim1 like 'product*';

Re: Problem with calculations of costs and sells

dusasuman — Tue, 29 Mar 2016 11:08:52 GMT

Looks like Your Model is not linked properly. Please check the same.

Re: Problem with calculations of costs and sells

jonathandienst — Tue, 29 Mar 2016 11:12:29 GMT

During the load, you could build a mapping table for the cost values for the components and then use ApplyMap to assign the costs to the products.

Another option would be to load the components with their costs in a separate table and associate the components to the products via the component ID. Then in a table using the products (and optionally the components) as dimensions, you could simply sum the costs.