Generate all possilbe Date/Account combinations and fill missing values

[#1]

Hey everyone,

got a quick question for you.

I have a table with Dates, Account Numbers and Balances.

Example:

Balances:
LOAD * INLINE [
Date, Account, Balance
01/04/2016, 123, 1000
02/04/2016, 123, 9700
05/04/2016, 123, 2000
08/04/2016, 123, 2500
01/04/2016, 555, 3000
03/04/2016, 555, 700
06/04/2016, 555, 2500
09/04/2016, 555, 5000
];

As you can see in the example, the rows are only available for the few days where the balance actually changed to a new value.

For my Dashboard I would want the balances to be calculated for all days in the range, for each account. Then, I want the balances to be filled with the previous value if it hasn't changed.

Note: The amount of accounts as well as the date range are dynamic and can change anytime.

The end result should be something like this

Date	Account	Balance
01.04.16	123	1000
01.04.16	555	3000
02.04.16	123	9700
02.04.16	555	3000
03.04.16	123	9700
03.04.16	555	700
04.04.16	123	9700
04.04.16	555	700
05.04.16	123	2000
05.04.16	555	700
06.04.16	123	2000
06.04.16	555	2500
07.04.16	123	2000
07.04.16	555	2500
08.04.16	123	2500
08.04.16	555	2500
09.04.16	123	2500
09.04.16	555	5000

I found easy ways to autogenerate the days missing but I struggling to do it for all date account combinations and with the Balance vallue filling.

Glad for any hint! Thanks!

[zhadrakas]

Hello Tim,

this script should work for you.

If a account has no value at the first possible date, he or she will get a Balance of 0.

If you want another Default value at this Point, Change this line:

LET vBalance = num(0);

----------------------------------------------

//Your Source Data (i put that in test.txt)
// vDate, vAccount, vBalance
// 01/04/2016, 123, 1000
// 02/04/2016, 123, 9700
// 05/04/2016, 123, 2000
// 08/04/2016, 123, 2500
// 01/04/2016, 555, 3000
// 03/04/2016, 555, 7000
// 06/04/2016, 555, 2500
// 09/04/2016, 555, 5000
// ];

Balances:
Load 
vDate as Date,
vAccount as Account,
vBalance as Balance
FROM (txt, codepage is 1252, embedded labels, delimiter is ',', msq);


//Create Tables for all different Date- and Account-Values to loop through them
Dates:  Load Distinct Date   Resident Balances order by Date desc;
Accounts: Load Distinct Account Resident Balances;

LET nAccounts = NoOfRows('Accounts');

//Loop through Source Data Table
For a = 1 to NoOfRows('Accounts')
LET vBalance = num(0);
LET vCurrentAccount = peek('Account', $(a)-1, 'Accounts');
For d = NoOfRows('Dates') to 1 step -1
LET vCurrentDate = peek('Date', $(d)-1, 'Dates');

//Create temporary Table to get Balance Value
tmp:
Load Balance Resident Balances where Account=$(vCurrentAccount) and Date='$(vCurrentDate)';

if(NoOfRows('tmp')=0) then
new:
Load '$(vCurrentDate)' as Date,
$(vCurrentAccount) as Account,
$(vBalance) as Balance
AutoGenerate (1);
end if

LET vBalance = peek('Balance', -1, 'tmp');
drop Table tmp;

Next
Next

//cleanup
LET a=;
LET d=;
LET nAccounts=;
LET vBalance=;
LET vCurrentAccount=;
LET vCurrentDate=;

[sunny_talwar · Accepted solution]

You can also try this:

Balances:

LOAD AutoNumber(Num(Date)&Account) as Key,

  *;

LOAD * INLINE [

Date, Account, Balance

01/04/2016, 123, 1000

02/04/2016, 123, 9700

05/04/2016, 123, 2000

08/04/2016, 123, 2500

01/04/2016, 555, 3000

03/04/2016, 555, 700

06/04/2016, 555, 2500

09/04/2016, 555, 5000

];

MinMax:

LOAD Date(Min +IterNo() - 1) as Date

While Min + IterNo() - 1 <= Max;

LOAD Min(Date) as Min,

  Max(Date) as Max

Resident Balances;

Left Join (MinMax)

LOAD DISTINCT Account

Resident Balances;

Concatenate (Balances)

LOAD Date,

  Account

Resident MinMax

Where not Exists(Key, AutoNumber(Num(Date)&Account));

FinalBalances:

NoConcatenate

LOAD Date,

  Account,

  If(Account = Previous(Account) and Len(Trim(Balance)) = 0, Peek('Balance'), Balance) as Balance

Resident Balances

Order By Account, Date;

DROP Tables MinMax, Balances;

[#4]

Thanks Sunny, this is exactly what I have been looking for! Thanks so much!

[#5]

Hey Tim, thanks for your help.

Tried to implement your code and it worked almost perfectly. Just the dates without any movement on any account  weren't created and filled with balances.

Maybe it's just a minor adjustment to your code.