To Control Sorting while Loading Data i.e in the Load Statement

[shannoypaul]

I am using a Previous() function for computing variance. The computation is dependent on the sorting sequence of the data being Loaded. The order by clause is not yielding the correct results as it is sorting it the right way on the o/p and not while loading the data. TIA, Shannoy

[sunny_talwar]

Something like this

Table:
LOAD * INLINE [
    Key, Created Date, Flag, Value
    123, 12/13/2018, 0, 15
    123, 12/13/2018, 1, 27.5
    123, 12/14/2018, 0, 28
    123, 12/14/2018, 1, 30
];

FinalTable:
LOAD *,
	 0 as New_Value
Resident Table
Where Flag = 0;

Concatenate (FinalTable)
LOAD *,
	 If(Key = Previous(Key), Value - Peek('Value'), 0) as New_Value;
LOAD *
Resident Table
Where Flag = 1
Order By Key, [Created Date];

DROP Table Table;