Previous value logic

[#1]

below is the sample data

ID	SubID	Actual
AB12CD	AB12CD	100
AB12CD	TD123C	200
AB12CD	PC12KT	250
TD123C	TD123C	300
TD123C	PC12KT	400
PC12KT	PC12KT	500
PC12KT	KC123T	600

when I select step =1, consider below example

for TD123C previos ID is 'AB12CD', now for ID AB12CD there are three value for SubId but I need to pick the subid which is equal to 'TD123C'.. So for TD123C  value should be 200.. So output for step 1 would be

ID	Value
AB12CD	0
TD123C	200
PC12KT	400

when step 2 is selected,consider below example

for PC12KT previos to previous ID is 'AB12CD', now for ID AB12CD there are three value for SubId but I need to pick the subid which is equal to 'PC12KT '.. So for PC12KT value should be 250.. So output for step 2 would be

ID	Value
AB12CD	0
TD123C	0
PC12KT	250

Note: I need to perform the calculation on front end.

[sunny_talwar · Accepted solution]

Check out the attached

Table:

LOAD *,

  AutoNumber(ID&SubID, 'Key') as Key,

  AutoNumber(ID, 'SNo') as IDSNo,

  AutoNumber(SubID, 'SNo') as SubIDSNo;

LOAD * INLINE [

    ID, SubID, Actual

    AB12CD, AB12CD, 100

    AB12CD, TD123C, 200

    AB12CD, PC12KT, 250

    TD123C, TD123C, 300

    TD123C, PC12KT, 400

    PC12KT, PC12KT, 500

    PC12KT, KC123T, 600

];

Left Join (Table)

LOAD *

Where IDSNo = SubIDSNo;

LOAD IDSNo + IterNo() as IDSNo,

  SubIDSNo,

  Actual as Prev_Actual,

  IterNo() as Step

Resident Table

While IterNo() <= 5;