Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear all,
I realize that the title is not very descriptive, but here is what I am trying to do:
I have a table:
LOAD * INLINE [
Reporting Date, Account no, Start_Date, End_Date, Balance, Category, Sub Category, Movement
31/01/2018, A, 31/01/2016, 31/01/2020, 100, Existing, 0
31/01/2018, B, 31/01/2016, 28/02/2018, 50, Existing, 0
28/02/2018, A, 31/01/2016, 31/01/2020, 80, Existing, Decrease, -20
28/02/2018, B, 31/01/2016, 28/02/2018, 0, Existing, Repaid, -50
28/02/2018, C, 28/02/2018, 31/01/2020, 10, New, New, 10
31/03/2018, A, 31/01/2016, 31/01/2020, 70, Existing, Decrease, -10
31/03/2018, C, 28/02/2018, 31/01/2020, 20, Existing, Increase, 10
];
What I have is; Reporting Date, Account no, Start_Date, End_Date and Balance.
What I want to calculate is Category, Sub Category and Movement.
If Category is New, the Movement should just be the Balance, but if the Category is Existing, then it should be the Movement from the prior date.
Then if the movement is negative then Decrease else increase.
I have tried a few ways to do this, but for some reason, I am having difficulties calculating the month on month movements.
Any help/guidance is appreciated.
Regards,
Aksel
Oh, I see. Then maybe like this
table1:
LOAD * INLINE [
Reporting Date, Account no, Start_Date, End_Date, Balance
31/01/2018, A, 31/01/2016, 31/01/2020, 100
31/01/2018, B, 31/01/2016, 28/02/2018, 50
28/02/2018, A, 31/01/2016, 31/01/2020, 80
28/02/2018, B, 31/01/2016, 28/02/2018, 0
28/02/2018, C, 28/02/2018, 31/01/2020, 10
31/03/2018, A, 31/01/2016, 31/01/2020, 70
31/03/2018, C, 28/02/2018, 31/01/2020, 20
];
table2: NoConcatenate
Load [Reporting Date], [Account no], [Start_Date], [End_Date], [Balance]
, if([Start_Date] = [Reporting Date], 'New', 'Existing') as Category
, if([Account no] <> Previous([Account no]) and [Start_Date] <> [Reporting Date], 0, Balance - Previous(Balance)) as Movement
, if([Start_Date] = [Reporting Date], 'New', If(Balance = 0, 'Repaid', if([Account no] = Previous([Account no]), If(RangeSum(Balance, -Previous(Balance)) > 0, 'Increase', 'Decrease'), '') ) ) as [Sub Category]
Resident table1 Order by [Account no], [Reporting Date];
Drop Table table1;
Hi Aksel. What about doing it in the load script
table1:
LOAD * INLINE [
Reporting Date, Account no, Start_Date, End_Date, Balance, Category, Sub Category, Movement
31/01/2018, A, 31/01/2016, 31/01/2020, 100, Existing, 0
31/01/2018, B, 31/01/2016, 28/02/2018, 50, Existing, 0
28/02/2018, A, 31/01/2016, 31/01/2020, 80, Existing, Decrease, -20
28/02/2018, B, 31/01/2016, 28/02/2018, 0, Existing, Repaid, -50
28/02/2018, C, 28/02/2018, 31/01/2020, 10, New, New, 10
31/03/2018, A, 31/01/2016, 31/01/2020, 70, Existing, Decrease, -10
31/03/2018, C, 28/02/2018, 31/01/2020, 20, Existing, Increase, 10
];
table2:
Load [Reporting Date], [Account no], [Start_Date], [End_Date], [Balance], [Category], [Sub Category]
,If(Category = 'New', Balance, If(Category = 'Existing', Previous(Movement), If(Movement < 0, 'Decrease', 'increase' ))) as Movement
Resident table1 Order by [Account no], [Reporting Date];
Drop Table table1;
The only thing, I did not get what Decrease increase are.
Thank you for this.
The issue is that the Categories, Movements and the sub Category needs to be calculated from the Balances.
First step to calculate the Category:
-Check if Start Date is equal to the Reporting Date, if so, mark Category as New, else Existing.
Second Step is to calculate the Movement:
-Calculate Month on Month movement (this is where I have the main issue)
For every account, I would like to create a new column of Movements where I have the difference from the prior month.
So for example for Account A in February you have a balance of 80 and in the following month (March) the balance reduces to 70 so the movement column should be -10.
If this number is negative then it is a decrease if positive then increase.
Hope it makes a bit more sense.
Oh, I see. Then maybe like this
table1:
LOAD * INLINE [
Reporting Date, Account no, Start_Date, End_Date, Balance
31/01/2018, A, 31/01/2016, 31/01/2020, 100
31/01/2018, B, 31/01/2016, 28/02/2018, 50
28/02/2018, A, 31/01/2016, 31/01/2020, 80
28/02/2018, B, 31/01/2016, 28/02/2018, 0
28/02/2018, C, 28/02/2018, 31/01/2020, 10
31/03/2018, A, 31/01/2016, 31/01/2020, 70
31/03/2018, C, 28/02/2018, 31/01/2020, 20
];
table2: NoConcatenate
Load [Reporting Date], [Account no], [Start_Date], [End_Date], [Balance]
, if([Start_Date] = [Reporting Date], 'New', 'Existing') as Category
, if([Account no] <> Previous([Account no]) and [Start_Date] <> [Reporting Date], 0, Balance - Previous(Balance)) as Movement
, if([Start_Date] = [Reporting Date], 'New', If(Balance = 0, 'Repaid', if([Account no] = Previous([Account no]), If(RangeSum(Balance, -Previous(Balance)) > 0, 'Increase', 'Decrease'), '') ) ) as [Sub Category]
Resident table1 Order by [Account no], [Reporting Date];
Drop Table table1;
Epic! Thank you very much!
I didn't know about the existence of 'Previous'