Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Experts,
I am looking for a solution where function assigns text like A or B or Values like 0 or 1 to the cumulative balance which are Falling to Zero. (I have done running cumulative balance using Peek)
May be below example should be explaining what i am trying to achieve. And i want to do this at Script level. The Field "Cumulative Balance" holds running balance and i would like to have result as shown in field "Expected result".
To keep it short: Solution i am looking is getting any text for all line items which are rolling to zero with condition that "COCODE" and "Assign" should be same.
COCODE | FY | Date | DOC# | LINITEM | Assign | Amnt | Cumulative Balance | SNO | Expected Result |
ABCD | 2020 | 2019-01-01 | 1 | 002 | XYZ | -5176.8 | -5,176.80 | 1 | A |
ABCD | 2020 | 2019-01-01 | 2 | 001 | XYZ | 5176.8 | 0.00 | 2 | A |
ABCD | 2020 | 2019-02-01 | 3 | 001 | XYZ | 5176.8 | 5,176.80 | 3 | A |
ABCD | 2020 | 2019-02-01 | 4 | 002 | XYZ | -5176.8 | 0.00 | 4 | A |
ABCD | 2020 | 2019-03-01 | 5 | 002 | XYZ | -4007.85 | -4,007.85 | 5 | A |
ABCD | 2020 | 2019-04-01 | 6 | 002 | XYZ | 4007.85 | 0.00 | 6 | A |
ABCD | 2020 | 2019-04-01 | 7 | 002 | XYZ | 5176.8 | 5,176.80 | 7 | A |
ABCD | 2020 | 2019-04-01 | 8 | 002 | XYZ | 5176.8 | 10,353.60 | 8 | A |
ABCD | 2020 | 2019-04-30 | 9 | 004 | XYZ | 4007.85 | 14,361.45 | 9 | A |
ABCD | 2020 | 2019-05-01 | 10 | 001 | XYZ | 4007.85 | 18,369.30 | 10 | A |
ABCD | 2020 | 2019-05-27 | 11 | 001 | XYZ | -4007.85 | 14,361.45 | 11 | A |
ABCD | 2020 | 2019-05-27 | 12 | 001 | XYZ | -5176.8 | 9,184.65 | 12 | A |
ABCD | 2020 | 2019-05-27 | 13 | 001 | XYZ | 5176.8 | 14,361.45 | 13 | A |
ABCD | 2020 | 2019-05-31 | 14 | 004 | XYZ | -4007.85 | 10,353.60 | 14 | A |
ABCD | 2021 | 2020-01-23 | 15 | 004 | XYZ | -10353.6 | 0.00 | 15 | A |
ABCD | 2021 | 2020-08-25 | 16 | 003 | XYZ | 12863.74 | 12,863.74 | 16 | A |
ABCD | 2021 | 2020-09-16 | 17 | 003 | XYZ | 12863.75 | 25,727.49 | 17 | A |
ABCD | 2021 | 2020-09-28 | 18 | 001 | XYZ | -5176.8 | 20,550.69 | 18 | A |
ABCD | 2021 | 2020-09-28 | 19 | 001 | XYZ | -5176.8 | 15,373.89 | 19 | A |
ABCD | 2021 | 2020-09-29 | 20 | 001 | XYZ | -15373.89 | 0.00 | 20 | A |
ABCD | 2021 | 2020-10-13 | 21 | 003 | XYZ | 12863.74 | 12,863.74 | 21 | B |
ABCD | 2021 | 2020-11-11 | 22 | 003 | XYZ | 12863.75 | 25,727.49 | 22 | B |
Found an alternate way but not sure its the best way to do it or not.
Left join to the above table
Load
COCODE&'|'&Assign as "Key1",
Max(SNO) as "Zero_SNO"
Resident [Data]
where Cumulative_WSL = 0
Group by COCODE,Assign
[FineData]:
Load Distinct*,
If(Zero_SNO>=SNO, 'A', 'B') as Status
Resident [Data];
Drop table [Data];