calculation (set analysis) during LOAD

[#1]

i was looking for something similar in the community, but couldnt find a solution.

basicly, i want to do calculations with certain units during load and store them in "unit" and "value".

i am trying to do some calculations during load, to store all the calculated data in a .qvd .

usually, this is a piece of cake, but this time, the data structure is totally different, and no approach i tried works.

ID	name	location	unit	value
1000	thing 1	here	temp	1
1000	thing 1	here	m2	2
1000	thing 1	here	volt	3
1000	thing 1	here	mw	4
1000	thing 1	here	etc. val	5

so, there are the collumns unit and value, containig the data which should be used in calculations.

i tried

(sum({<[unit]={'volt'}>}value))*(sum({<[unit]={'mw'}>}value)) AS Energy;

but this doesnt work during LOAD.

the calculations should be added to the table like the old units and values

ID	name	location	unit	value
1000	thing 1	here	energy	12
1000	thing 1	here	someunit	15

Any suggestions ?

Please enlight me.

[swuehl · Accepted solution]

Not really sure what you are after, I am assuming that you want to group your results by ID, Name and location, then a simple approach could look like:

INPUT:

LOAD ID,

     name,

     location,

     unit,

     value

FROM

[http://community.qlik.com/thread/39879?tstart=0]

(html, codepage is 1252, embedded labels, table is @1);

MW:

left join LOAD ID, name, location, sum(value) as SumMW Resident INPUT where unit = 'mw' group by ID, name, location ;

Volt:

left join LOAD ID, name, location, sum(value) as SumVolt Resident INPUT where unit = 'volt' group by ID, name, location ;

Result:

LOAD ID, name, location, unit, value, SumMW * SumVolt as Energy resident INPUT;

drop table INPUT;

Is this what you were looking for?

edited left joins