Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Hi,
Please find attached. hope that helps.
May be this:
Perday=Aggr(sum(perday), Time out)
Aggregate=Aggr(sum(Aggregate), Time out)
Put this in expression..Hope it helps
Sachin
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;
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
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.