Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
rvc_121985
Contributor III
Contributor III

Bringing Text or Values to running balance with condition

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.

COCODEFYDateDOC#LINITEMAssignAmntCumulative BalanceSNOExpected Result
ABCD20202019-01-011002XYZ-5176.8-5,176.801A
ABCD20202019-01-012001XYZ5176.80.002A
ABCD20202019-02-013001XYZ5176.85,176.803A
ABCD20202019-02-014002XYZ-5176.80.004A
ABCD20202019-03-015002XYZ-4007.85-4,007.855A
ABCD20202019-04-016002XYZ4007.850.006A
ABCD20202019-04-017002XYZ5176.85,176.807A
ABCD20202019-04-018002XYZ5176.810,353.608A
ABCD20202019-04-309004XYZ4007.8514,361.459A
ABCD20202019-05-0110001XYZ4007.8518,369.3010A
ABCD20202019-05-2711001XYZ-4007.8514,361.4511A
ABCD20202019-05-2712001XYZ-5176.89,184.6512A
ABCD20202019-05-2713001XYZ5176.814,361.4513A
ABCD20202019-05-3114004XYZ-4007.8510,353.6014A
ABCD20212020-01-2315004XYZ-10353.60.0015A
ABCD20212020-08-2516003XYZ12863.7412,863.7416A
ABCD20212020-09-1617003XYZ12863.7525,727.4917A
ABCD20212020-09-2818001XYZ-5176.820,550.6918A
ABCD20212020-09-2819001XYZ-5176.815,373.8919A
ABCD20212020-09-2920001XYZ-15373.890.0020A
ABCD20212020-10-1321003XYZ12863.7412,863.7421B
ABCD20212020-11-1122003XYZ12863.7525,727.4922B

 

 

Labels (1)
1 Reply
rvc_121985
Contributor III
Contributor III
Author

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