1 Reply Latest reply: Nov 29, 2011 11:47 AM by Stefan Wühl

calculation (set analysis) during LOAD

Andreas Dippel Nov 29, 2011 11:42 AM

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.

Re: calculation (set analysis) during LOAD

Stefan Wühl Nov 29, 2011 11:47 AM (in response to Andreas Dippel)

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
Like Show 0 Likes (0)

Actions