Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello All,
I have a scenario where I have to subtract the value from current record against the previous record. Below is the example. I need to achieve the results as in column C
A | B | C |
1 | 1000 | |
1 | 200 | 800 |
1 | 200 | 600 |
1 | 500 | 100 |
2 | 1000 | |
2 | 300 | 700 |
2 | 100 | 600 |
2 | 50 | 550 |
Hi @Krishnan92 ,
If you want to do this in the script, you can use the following expression:
IF( PEEK(A) = A, IF(ISNULL( PEEK(C) ), PEEK(B) - B, PEEK(C) - B)) AS C
Result:
Hi @Krishnan92 ,
If you want to do this in the script, you can use the following expression:
IF( PEEK(A) = A, IF(ISNULL( PEEK(C) ), PEEK(B) - B, PEEK(C) - B)) AS C
Result:
Hello @ggijben thank you so much for your response. But I have fields A and B alone. C should be my output derived field.
Hi @Krishnan92 ,
Yes, that is correct. Column C will be created with the formula in my previous post. You will only need columns A + B. See the attached app for an example.
@ggijben Thank you! It worked perfectly. Also do you know how to add all the ID 1 values together and then do the subtraction one by one as mentioned above. Like the example below
A | B | C | |
1 | 1000 | 1900 | >Sum of all ID 1 Values |
1 | 200 | 1700 | |
1 | 200 | 1500 | |
1 | 500 | 1000 | |
2 | 1000 | ||
2 | 300 | 700 | |
2 | 100 | 600 | |
2 | 50 | 550 |