topic Re: Data Table in QlikView

Data Table

evansabres — Fri, 10 Feb 2017 19:04:15 GMT

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.

Re: Data Table

krishnacbe — Fri, 10 Feb 2017 19:22:15 GMT

Hi,

Please find attached. hope that helps.

Re: Data Table

sdmech81 — Fri, 10 Feb 2017 19:29:44 GMT

May be this:

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

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

Put this in expression..Hope it helps

Sachin

Re: Data Table

maxgro — Fri, 10 Feb 2017 19:41:46 GMT

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;

Re: Data Table

sdmech81 — Fri, 10 Feb 2017 19:46:10 GMT

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

Re: Data Table

Fri, 10 Feb 2017 20:15:05 GMT

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.