Data Table

[evansabres]

I have a data table that looks like:

DATE	TIME OUT	PER DAY	AGGREGATE
2012-2-12	1%	37	37
2012-2-12	1%	49	49
2012-2-12	2%	4	4
2012-2-12	2%	10	10
2012-2-12	3%	3	3

I need to transform this in the LOAD script come out as the following:

DATE	TIME OUT	PER DAY	AGGREGATE
2012-2-12	1%	86	86
2012-2-12	2%	14	100
2012-2-12	3%	3	303

Essentially I need to combine the per day totals to each unique instance of time out (multiple 1% become one row) as well as aggregate the sum into a running total.

Any help is appreciated.

[krishnacbe]

Hi,

Please find attached. hope that helps.

[sdmech81]

May be this:

Perday=Aggr(sum(perday), Time out)

Aggregate=Aggr(sum(Aggregate), Time out)

Put this in expression..Hope it helps

Sachin

[maxgro]

You asked in script

tmp:

LOAD date(max(date#(DATE, 'YYYY-M-DD')), 'YYYY-M-DD') as DATE,

     [TIME OUT],

     sum([PER DAY]) as [PER DAY]

FROM

[https://community.qlik.com/thread/249533]

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

Group By [TIME OUT];

final:

LOAD

  *,

  RangeSum([PER DAY], Peek('AGGREGATE')) as AGGREGATE

Resident tmp

Order By [TIME OUT];

DROP Table tmp;

[sdmech81]

If in script follow mossimo ,in expression hope mine helps..

But u can do it in expression so tht your raw data remains same for other operations..

Sachin

[#6]

Unfortunately that isn't working. It all needs to be done with in the scripting portion so that our end product is a clean data set available for visualization.

We've taken the initial data set and transformed it so that we're left with multiple percentages that represent the time away from an event each with a unique number

1% - 2

1% - 3

1% - 6

In order to get the data set proper - we need to be left with one row for each percent such as:

1% - 11

This will allow us to build a full 0-100% range for each event in our data set.

The second part is that we need to have those numbers aggregate in another column to create a running total.