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

    calculation (set analysis) during LOAD

      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.

       

      IDnamelocationunitvalue
      1000thing 1heretemp1
      1000thing 1herem22
      1000thing 1herevolt3
      1000thing 1heremw4
      1000thing 1hereetc. val5

       

      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

      IDnamelocationunitvalue
      1000thing 1hereenergy12
      1000thing 1heresomeunit15

       

      Any suggestions ?

       

       

      Please enlight me.

        • Re: calculation (set analysis) during LOAD
          Stefan Wühl

          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