Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear all,
I have a table like this:
SRC:
LOAD * INLINE [
Dim1, Dim2, Dim3, Thema1, Thema2, MSNr
400, G11, BF, 10, 20, 01.16
400, G11, BF, 15, 152, 02.16
400, G11, BF, 99, 99, 03.16
500, G11, BF, 1, 2, 01.16
500, F10, BF, 30, 21, 02.16
500, F10, BF, 1, 1, 05.16
];
and need to get a new column SUM_Thema 1 which sums up
column Thema1 IF Derivat,ISt,MS are equal - SORTED BY Date
Thanks for your help
Oliver
Derivat | ISt | MS | MSNr | Thema1 | Thema2 | SUM Thema1 |
F10 | 500 | BF | 01.16 | 30 | 21 | 30 |
F10 | 500 | BF | 02.16 | 1 | 1 | 31 |
G11 | 400 | BF | 01.16 | 10 | 20 | 10 |
G11 | 400 | BF | 02.16 | 15 | 152 | 25 |
G11 | 400 | BF | 03.16 | 99 | 99 | 124 |
G11 | 500 | BF | 05.16 | 1 | 2 | 1 |
Try this out:
SRC:
LOAD ISt,
Derivat,
MS,
Thema1,
Thema2,
Date#(MSNr&'.2016', 'MM.DD.YYYY') as MSNr
INLINE [
ISt, Derivat, MS, Thema1, Thema2, MSNr
400, G11, BF, 10, 20, 01/16/2016
400, G11, BF, 15, 152, 02/16/2016
400, G11, BF, 99, 99, 03/16/2016
500, G11, BF, 1, 2, 01/16/2016
500, F10, BF, 30, 21, 02/16/2016
500, F10, BF, 1, 1, 05/16/2016
];
FinalSRC:
LOAD *,
If(ISt = Previous(ISt) and Derivat = Previous(Derivat) and MS = Previous(MS), RangeSum(Peek('SUM Thema 1'), Thema1), Thema1) as [SUM Thema 1]
Resident SRC
Order By ISt, Derivat, MS, MSNr ASC;
DROP Table SRC;
Try this out:
SRC:
LOAD ISt,
Derivat,
MS,
Thema1,
Thema2,
Date#(MSNr&'.2016', 'MM.DD.YYYY') as MSNr
INLINE [
ISt, Derivat, MS, Thema1, Thema2, MSNr
400, G11, BF, 10, 20, 01/16/2016
400, G11, BF, 15, 152, 02/16/2016
400, G11, BF, 99, 99, 03/16/2016
500, G11, BF, 1, 2, 01/16/2016
500, F10, BF, 30, 21, 02/16/2016
500, F10, BF, 1, 1, 05/16/2016
];
FinalSRC:
LOAD *,
If(ISt = Previous(ISt) and Derivat = Previous(Derivat) and MS = Previous(MS), RangeSum(Peek('SUM Thema 1'), Thema1), Thema1) as [SUM Thema 1]
Resident SRC
Order By ISt, Derivat, MS, MSNr ASC;
DROP Table SRC;