topic AW:Re: AW:Crosstable in QlikView

Crosstable

Thu, 06 Jan 2011 16:21:25 GMT

I it possible to pivot my data (by script) as the following example? On the left is the data, in the blue box is the desired format.

AW:Crosstable

rbecher — Thu, 06 Jan 2011 16:34:00 GMT

Hi,

it's not just pivoting (or transposing) - it's also an aggregation.

I would do a script loop over the year and aggregate the fields YR-1, YR-2 and YR-3 with

LOAD ID
, <the_loop_year> as YEAR
, sum(<Field>) as Amount, sum(if(YEAR=<the_loop_year>, Amount, Null()) as Amount
, sum(<Field>) as Amount, sum(if(YEAR=<the_loop_year> -1, Amount, Null()) as Amount_YR-1
, sum(<Field>) as Amount, sum(if(YEAR=<the_loop_year> -2, Amount, Null()) as Amount_YR-2
, sum(<Field>) as Amount, sum(if(YEAR=<the_loop_year> -3, Amount, Null()) as Amount_YR-3
from...
group by ID
where YEAR >= <the_loop_year> and YEAR <= <the_loop_year> -3;

if this is a fixed amount of years.

- Raf

AW:Crosstable

Fri, 07 Jan 2011 13:28:21 GMT

Suppose i don''t have multiple values for each year so i dont need the aggregation. What is the best way to pivot/transpose the values?? is there a scipt function to do this??

AW:Re: AW:Crosstable

rbecher — Fri, 07 Jan 2011 14:01:24 GMT

then you could use above function in chart, see attachment

- Ralf