Let me know if you can use something like below: Suppose you have a customer table with
| customername | availablebalance | accounttype |
| name1 | 10000 | current |
| name1 | 20000 | saving |
| name1 | 1000 | other |
| name2 | 15000 | current |
| name2 | 18000 | saving |
| name2 | 1200 | other |
and another table to store the minimumbalance.
| minbalcurrent | minbalsavings | minbalother |
| 10000 | 20000 | 1000 |
In your load script you can create flags to indicate which customer has available balance below than minimumbalance based on the account type:
tmp:
LOAD minbalcurrent,
minbalsavings,
minbalother
FROM
customer.xlsx
(ooxml, embedded labels, table is minbal);
let vMinBalCurrent = peek('minbalcurrent',0,'tmp');
let vMinBalSaving = peek('minbalsavings',0,'tmp');
let vMinBalOther = peek('minbalother',0,'tmp');
DROP Table tmp;
customer:
LOAD customername,
availablebalance,
accounttype,
if(accounttype='current' and availablebalance< '$(vMinBalCurrent)',1,0) as currentbalflag,
if(accounttype='saving' and availablebalance< '$(vMinBalSaving)',1,0) as savingsbalflag,
if(accounttype='other' and availablebalance< '$(vMinBalOther)',1,0) as otherbalflag
FROM
customer.xlsx
(ooxml, embedded labels, table is customer);
Now on the UI you can count the number of customers having available balance lesser than the minimum balance for specific account type just using the flags. Or you can display those customers name who has at least one of the flags as 1.
count({<savingsbalflag={1} >} customername)
count({<currentbalflag={1} >} customername)
count({<otherbalflag={1} >} customername)
