Expression for filtering stock having balance more than 0

[Neeraj_Malik]

Dear Friends,

I have the following table in my database

I'm trying to have an expression to filter all/list of ticker symbols with specifc stock exchange and investment type for the specific client a/c number that has the stock balance more than 0. The purpose of the filtered list is to use that as validation for displaying the filtereded list to the user for Share purchase transaction.

I'm using the following expression
Select(Stock_Market[Ticker_Symbol], AND([Stock_Exchange]=[_thisrow].[Stock_Exchange], [Client_A/c_Number]=[_thisrow].[Client_A/c_Number], [Investment_Type]=[_thisrow].[Investment_Type], [Stock_Balance]>0))

However result of this expression is also inlcuding the ticker symbol CAFCA.zw with 0 stock balance as against the expected result of filtering only OMTT.zw that has stock balance more than 0.

Can someone help me with correct expression pls? @Steve @Suvrutt_Gurjar @WillowMobileSys @Marc_Dillon @LeventK 

[Chris_Jeal1]

But the stock balance for CAFCA.zw is >0 so your expression is returning the correct info isn't it?

[WillowMobileSys]

If I understand correctly...because the ticker "CAFCA.zw" has a Balance of zero in one of the rows, then you don't want ANY of those rows returned for that ticker.  The expression cannot handle that.  It doesn't "know" the relationship between similar rows.

The question is...Why have 2 rows for that same ticker?  Are you trying to maintain some kind of history?  If NOT then manage the Purchase Qty, Sales Qty and Stock Balance, within the same row.

If you are trying to maintain some history, then I would recommend dividing between two tables with a Parent/Child relationship.  The Parent has all the columns shown EXCEPT Purchase and Sales.  The Child table would have REF back to the Parent, columns for Purchase and Sales, and columns for Date/Time history.    The Balance column is re-compted each time a Child entry is made.

I hope this helps!

[Neeraj_Malik · Accepted solution]

Thanks @WillowMobileSys for sparing time to look into my problem.

The table shown in  problem description is transaction table capturing all the sale & purchase txn and maintaining the running balance in  column Stock_Balance.

My end objective is to achieve a validation check where only the stocks having running balance (Stock_Balance) more than 0 for that specific client account number and the stock exchange should be displayed for the sale transaction. That's where I wanted to use this expression which didn't given the desired result.

However I manage to figure out another way to do it which may not be the efficient but manageable in my case.

I have added another column Current_Stock_Balance having current stock balance irrespective of the txn typ or date, which is recomputed every time a row is added. And using that column in the same expression as per following

Select(Stock_Market[Ticker_Symbol], AND([Stock_Exchange]=[_thisrow].[Stock_Exchange], [Client_A/c_Number]=[_thisrow].[Client_A/c_Number], [Investment_Type]=[_thisrow].[Investment_Type], [Current_Stock_Balance ]>0))

Open to any other effiecient way to do it, till the current workaround would suffice. Once again thanks for your time.