Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
mahnoor1279
Contributor III
Contributor III

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

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

Labels (5)
1 Reply
Sebastian_Dec
Creator II
Creator II

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.

Thanks & Regards,
Please close the thread by marking correct answer & give likes if you like the post.