This content has been marked as final. Show 3 replies
SourceData : LOAD DimA, Date, DimB, if(previous(DimB) = DimB, peek('Counter'),rangesum(1,peek('Counter'))) as Counter INLINE [ DimA, Date, DimB 18524, 01/02/2010, MAALC 18524, 13/02/2010, MAALC ...etc 12345, 22/02/2014, MAIBZ 12345, 23/02/2014, MAIBZ ]; Result: load DimA, DimB, Counter, min(Date) as StartDate, max(Date) as EndDate Resident SourceData group by DimA, DimB, Counter; //optionally: // Drop field Counter;
This does assume that the data is already ordered correctly. If it isn't you'll need an extra resident load to fix the order first.