Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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