How to get only those customer who have some amounts on previous day but having zero amount on to date

[mahnoor1279]

Hey everyone,

I want to  get only customers if they were having some amount of balance  where balance = (SUM(OUTFLOW) - SUM(INFLOW)),but on to date their balance is Zero.

TEST1:

LOAD

CUST_ACCT_SNO,
OUTFLOW,

INFLOW,
LEDGER_DATE


FROM [lib://(ahiml-main_qsense)/TRANSACTION.qvd]
(qvd);

[Sebastian_Dec]

Hey, try this, but in Your data none of the clients meet the conditions.

Data_tmp:
LOAD
  CUST_ACCT_SNO,
  LEDGER_DATE,
  SUM(INFLOW) as INFLOW,
  SUM(OUTFLOW) as OUTFLOW,
  SUM(OUTFLOW)-SUM(INFLOW) as BALANCE
FROM [lib://redemption_test.xlsx]
(ooxml, embedded labels, table is Sheet1)
GROUP BY CUST_ACCT_SNO, LEDGER_DATE;

Data:
LOAD
  CUST_ACCT_SNO,
  LEDGER_DATE,
  INFLOW,
  OUTFLOW,
  BALANCE
RESIDENT Data_tmp
WHERE 
	(Date(LEDGER_DATE) = Today() AND BALANCE = 0)
  	AND (Date(LEDGER_DATE) = Today()-1 AND BALANCE > 0);
  
Drop table Data_tmp

Prepare xlsx data for about 20 clients, of which be sure that 3-4 meet the conditions.