Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
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.
Can you share how your ID looks like?
It's Ab12ef, abce12, cd12ce etc.. it's random alphanumeric string in which there is no pattern
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
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.
please help stalwar1, swuehl gwassenaar
any update?
Responded to your new thread: