Logic help needed

[#1]

I have below data set.

ID	SubID	Actual
1	1	100
1	2	200
1	3	250
2	2	300
2	3	400
3	3	500
3	4	600

Output needed (I want to calculate it in front end only)

ID	Value
1	0
2	200
3	400

Logic: For ID 2, I want to see previous ID & subid should be equal to current id

          example, For ID 2, I will see the previous ID 1 and Pick subid =2 value

[#2]

Yes, Input will be like below

Step

1

2

3

4

5

based on step selection, our calculation should work. I think this is possible only from front end.

[sunny_talwar]

Try this:

Table:

LOAD *,

  AutoNumber(ID&SubID) as Key;

LOAD * INLINE [

    ID, SubID, Actual

    1, 1, 100

    1, 2, 200

    1, 3, 250

    2, 2, 300

    2, 3, 400

    3, 3, 500

    3, 4, 600

];

Left Join (Table)

LOAD *

Where ID = SubID;

LOAD ID + IterNo() as ID,

  SubID,

  Actual as Prev_Actual,

  IterNo() as Step

Resident Table

While IterNo() <= 5;

When Step = 2

When Step = 1

[#4]

stalwar1‌ thanks a lot, but issue with me is, ID is actually in characters(iterno will not work i guess), I would not be able to share the data due to which I prepared this data. Sorry for the inconvenience.

[sunny_talwar]

Can you share how your ID looks like?

[#6]

It's Ab12ef, abce12, cd12ce etc.. it's random alphanumeric string in which there is no pattern

[sunny_talwar]

So then what is Step1 and Step2? I guess you will have to provide a better sample for us to look at, this is something I can't really comment about without looking at

[#8]

See this 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

for Step 1, Go to previous ID(1 step),  check subid which is equal to below(1 step) id

ID	Value
AB12CD	0
TD123C	200
PC12KT	400

for step 2, Go to previous ID(2 step),  check subid which is equal to below (2 step) id

ID	Value
AB12CD	0
TD123C	0
PC12KT	250

Logic is the same which I have explained in OP.

[#9]

please help stalwar1‌, swuehl‌ gwassenaar‌

[#10]

any update?

[sunny_talwar]

Responded to your new thread:

Re: Previous value logic