How to create Closing balance and Opening balance fro all Days in a month.

[Anonymous]

Hi All,

How to create Closing balance  and Opening balance fro all Days in a month.

Closing balance = credit - Debit, Yesterday's Closing balance=Today's Opening balance.

i created Closing balance and Opening balance for the entries available in Data table. But  the entries is not there in the Data table also should display last Closing balance and Opening balance for that days.(7 th jan 2017 to 31st jan 2017)

Thanks,

Krishna.

[ahaahaaha · Accepted solution]

Hi Krishna,

May be like this

Table1:

LOAD*,

Date(Date#(Date_d1, 'MM/DD/YYYY')) as Date_d;

LOAD*Inline

[Cust_id, Date_d1, Credit, Debit, Opening_bal, Closing_bal

1, 1/1/2017, 10000, 0, 0, 10000

1, 1/2/2017, 2000, 3000, 10000, 9000

1, 1/3/2017, 5000, 0, 9000, 14000

1, 1/4/2017, 0, 200, 14000, 13800

1, 1/5/2017, 3000, 0, 13800, 16800

1, 1/6/2017, 0, 400, 16800, 16400

2, 1/1/2017, 50000, 0, 0, 50000

2, 1/8/2017, 0, 2000, 50000, 48000

2, 1/9/2017, 30000, 0, 48000, 78000

2, 1/10/2017, 0, 3000, 78000, 75000];

NoConcatenate

Dates:

LOAD

Date(Date('01.01.2017') + RecNo() - 1, 'MM/DD/YYYY') as Date_d1

autogenerate Date(Date('31.01.2017') - Date('01.01.2017') + 1, 'MM/DD/YYYY');

Left Join(Dates)

LOAD Distinct

Cust_id as Cust_id1

Resident Table1;

Join (Table1)

LOAD

Date_d1 as Date_d,

Cust_id1 as Cust_id

Resident Dates;

Result:

NoConcatenate

Load

Cust_id,

Date_d,

If( IsNull(Credit) And IsNull(Debit), Peek(Credit), Credit ) as Credit,

If( IsNull(Credit) And IsNull(Debit), Peek(Debit), Debit ) as Debit,

If( IsNull(Credit) And IsNull(Debit), Peek(Opening_bal), Opening_bal ) as Opening_bal,

If( IsNull(Credit) And IsNull(Debit), Peek(Closing_bal), Closing_bal ) as Closing_bal

Resident Table1

Order By Cust_id, Date_d ;

Drop Table Table1;

Result

Example at attached file.

Regards,

Andrey

[Anonymous]

Hi  Andery K,

Thanks for  your answer. It looks good.

Regards,

Krishna

[ahaahaaha]

Was glad to help you.

Regards,

Andrey