Row-wise Multiplication of values in different tables

Wed, 18 Mar 2015 16:34:24 GMT

Hello all,

I'm a Student working on a research project for environmental Monitoring and I'm absolutely new to Qlikview... have been stuck with a problem for 3 days now and it's driving me insane. Hope someone can give me some hints as it can't really be that hard:

There are 2 Tables, that are loaded from .qvo files exported by another QV Document.

Table 1 defines Energy Sources (7 Rows) and their Carbon emission factors, the id is the Key:

energy_source_id | energy_source_name | energy_source_emissionfactor

1 Nuclear 0,312

2 Coal 0,986

3 Renewable 0,121

...

Table 2 defines Production Sites, the share of the energy sources in their consumption and the price of the energy, it has 2 keys - the energy_source_id and the site_id (Site_id links it to the other tables in the Document)

site_id | energy_source_id | energy_source_share (%) | energy_source_price

1 1 30,00 168,89

1 2 40,00 178,46

1 3 10,00 156,35

1 4 20,00 162,46

2 1 30,00 168,89

2 2 40,00 178,46

2 3 10,00 156,35

2 4 20,00 162,46

3 1 30,00 168,89

3 2 40,00 178,46

3 3 10,00 156,35

3 4 20,00 162,46

and so on

Now I need to calculate the effective carbon factor and energy price for each site. Therefor I need to get the relevant Rows for each site out of table 2, do a row-wise multiplication of values in different tables and then I need the sum of the results... the share has to be divided by 100 (-> %)

effective_carbon_factor = SUM (energy_source_share*0,01 * energy_source_emissionfactor)

effective_energy_price = SUM (energy_source_share*0,01 * energy_source_price)

--> The part in the SUM needs to be row-wise, how does that work??!

The results than should be stored in a third table (Sites) that holds the site_id and site_name:

site_id | site_name | energy_price | emission_factor

1 Munich 168,89 0,462

2 New York 178,46 0,486

3 London 156,35 0,678

The Calculation has to be done in the Script and the original tables must not be changed, as they are used by the rest of the document. I've tried to accomplish the calculation with several For loops which got me some hundred lines of code which are not working at all...

Any Help highly appreciated!!!

Thanks

My Code at the moment (Tables 1 & 2 are loaded earlier):

// -- Calculation of effective Price and Emission Factors --------------------//

For site='1' to NoOfRows('Sites')

// copy relevant rows for each site in temp table SiteMix ----------------------------//

SiteMix:

LOAD energy_source_price, energy_source_share Resident EnergyMix Where site_id=$(site);

// calculate relative values (workaround for row-wise Multiplication) and save to Table RelFactors ----------------------------//

For source = 1 to NoOfRows('EnergySources')

Let carbon_factor = FieldValue('energy_source_carbonfactor',$(source));

Let source_price = FieldValue('energy_source_price',$(source));

Let source_share = peek('energy_source_share', $(source),'SiteMix');

Let carbonfactor_rel = NUM($(carbon_factor))*NUM($(source_share))*0.01;

Let price_rel = $(source_price))*NUM($(source_share))*0.01;

RelFactors:

LOAD * INLINE [

energy_source, carbonfactor, price

'$(source)', '$(carbonfactor_rel)', '$(price_rel)'

];

Next source;

// calculate sum of relative values and save to Table Sites ----------------------------//

Let carbonfactor_total= Sum(carbonfactor_rel);

Let price_total= Sum(price_rel);

DROP Table RelFactors;

Outer Join (Sites)

LOAD * INLINE [

site_id, site_carbonfactor, site_price

'$(site)', '$(carbonfactor_total)', '$(price_total)'

];

DROP Table SiteMix;

Next site;

Re: Row-wise Multiplication of values in different tables

Gysbert_Wassenaar — Thu, 19 Mar 2015 13:45:44 GMT

See attached qvw.

topic Row-wise Multiplication of values in different tables in QlikView

Row-wise Multiplication of values in different tables

Re: Row-wise Multiplication of values in different tables