Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have the following table with end of day account balance:
AccountNumber
Bal_Date (ex 9/1/2019)
Balance
Here is a small sample of data:
00000000001 - $1000.00 - 8/1/2019 - Aug - 2019
00000000001 - $900.00 - 8/2/2019 - Aug 2019
00000000001 - $1000.00 - 8/3/2019 - Aug 2019
00000000001 - $900.00 - 8/4/2019
-- more for August 2019
00000000001 - $1000.00 - 9/1/2019 - Sep - 2019
00000000001 - $1000.00 - 9/2/2019 - Sep - 2019
00000000001 - $1000.00 - 9/3/2019 - Sep - 2019
00000000001 - $1000.00 - 9/4/2019 - Sep - 2019
Now I need to create a new table that will hold the average monthly balance for each AccountNumber/Month+Year. Since I will have numerous months worth of data, there will be 1 record for each AccountNumber/Month+Year, like this:
00000000001 - $1000.00 - Aug 2019
00000000001 - $900.00 - Sep 2019
Since I have millions of records, this seems like a task to be run in the load script but I can't get the above table. I can get the AccountNumber/Month+Year and the AccountNumber+Avg Monthly Balance but not all 3. I've tried many iterations of avg, aggr, etc and still nothing. Here is my latest script. It generates an error but I want to include it to give you an idea of what I am trying to do:
Balance:
LOAD AccountNumber,
Bal_Date,
Balance,
Month(Bal_Date) as BalanceMonthName,
Year(Bal_Date) as BalanceYearName
FROM [Data\Up To 10-04-2019\Balance.qvd] (qvd);
// Calculate avg monthly balance by month/customer.
AverageCustomerBalance:
Load Distinct AccountNumber,
BalanceMonthName,
BalanceYearName,
Avg(Balance) as AverageMonthlyBalance
Resident Balance
group by AccountNumber;
Thanks!
I figured this out about 10 minutes after posting (see below) although I'm open to suggestions for better ways to do it:
AverageCustomerBalance:
LOAD distinct AccountNumber,
Avg(Balance) as AverageBalance,
month(Bal_Date) as MonthName,
year(Bal_Date) as YearName
Resident Balance
GROUP BY AccountNumber,
month(Bal_Date),
year(Bal_Date);
I figured this out about 10 minutes after posting (see below) although I'm open to suggestions for better ways to do it:
AverageCustomerBalance:
LOAD distinct AccountNumber,
Avg(Balance) as AverageBalance,
month(Bal_Date) as MonthName,
year(Bal_Date) as YearName
Resident Balance
GROUP BY AccountNumber,
month(Bal_Date),
year(Bal_Date);
I couldn't get it to work in a chart and with millions of records, I thought it would be more efficient doing it in the load script.
it would be faster but less flexible if you do in script e.g. if you want to filter this average by some other dimension.
But as this is at a i think low granular level (account number) it should be ok.
It should work front end. What did you try?
p.s. million rows is generally easily handled by qlik