Bringing Text or Values to running balance with condition

RC_121985 — Mon, 20 Dec 2021 21:08:46 GMT

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

Re: Bringing Text or Values to running balance with condition

RC_121985 — Sat, 19 Dec 2020 11:19:59 GMT

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];

topic Re: Bringing Text or Values to running balance with condition in App Development

Bringing Text or Values to running balance with condition

Re: Bringing Text or Values to running balance with condition