Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I wish to create a resident table which summarizes invoice information which has been previously loaded into a resident table which creates a current week value and previous week value allowing me to compare and include within an extension I am using.
As part of the load I am also including the week number (using AutoNumber(weekyear(INVOICE_DATE) &'|' & week(INVOICE_DATE)) as WeekSerial)
CUSTOMER_TYPE | INVOICE_AMOUNT | INVOICE_DATE | WeekSerial |
Type A | 908 | 01/01/2018 | 1 |
Type B | 6466 | 01/01/2018 | 1 |
Type C | 63 | 01/01/2018 | 1 |
Type D | 256636 | 01/01/2018 | 1 |
Type A | 98798 | 08/01/2018 | 2 |
Type B | 34235 | 08/01/2018 | 2 |
Type C | 25676 | 08/01/2018 | 2 |
Type D | 74747 | 08/01/2018 | 2 |
Type A | 2525 | 15/01/2018 | 3 |
Type B | 63678 | 15/01/2018 | 3 |
Type C | 856 | 15/01/2018 | 3 |
Type D | 5799 | 15/01/2018 | 3 |
Type A | 54574 | 23/01/2018 | 4 |
Type B | 848989 | 23/01/2018 | 4 |
Type C | 3633 | 23/01/2018 | 4 |
Type D | 4367 | 23/01/2018 | 4 |
I want the resident table to group by CUSTOMER_TYPE but only including the Current & Previous Weeks summed totals.
So assuming we are in week 4 of the month I would expect the following in a new resident table, I dont know how to work out the previous week or even select that to summarise into a column.
CUSTOMER_TYPE | CURRENT_WEEK | PREVIOUS_WEEK |
TYPE_A | 54574 | 2525 |
TYPE_B | 848989 | 63678 |
TYPE_C | 3633 | 856 |
TYPE_D | 4367 | 5799 |
Is this even the best way to do it?
Any help appreciated.
Thanks
Martin
May be try this
Table:
LOAD * INLINE [
CUSTOMER_TYPE, INVOICE_AMOUNT, INVOICE_DATE, WeekSerial
Type A, 908, 01/01/2018, 1
Type B, 6466, 01/01/2018, 1
Type C, 63, 01/01/2018, 1
Type D, 256636, 01/01/2018, 1
Type A, 98798, 08/01/2018, 2
Type B, 34235, 08/01/2018, 2
Type C, 25676, 08/01/2018, 2
Type D, 74747, 08/01/2018, 2
Type A, 2525, 15/01/2018, 3
Type B, 63678, 15/01/2018, 3
Type C, 856, 15/01/2018, 3
Type D, 5799, 15/01/2018, 3
Type A, 54574, 23/01/2018, 4
Type B, 848989, 23/01/2018, 4
Type C, 3633, 23/01/2018, 4
Type D, 4367, 23/01/2018, 4
];
Left Join (Table)
LOAD Max(WeekSerial) as Max,
Max(WeekSerial, 2) as Max2
Resident Table;
AggrTable:
LOAD CUSTOMER_TYPE,
Sum(If(WeekSerial = Max, INVOICE_AMOUNT)) as CURRENT_WEEK,
Sum(If(WeekSerial = Max2, INVOICE_AMOUNT)) as PREVIOUS_WEEK
Resident Table
Group By CUSTOMER_TYPE;
May be try this
Table:
LOAD * INLINE [
CUSTOMER_TYPE, INVOICE_AMOUNT, INVOICE_DATE, WeekSerial
Type A, 908, 01/01/2018, 1
Type B, 6466, 01/01/2018, 1
Type C, 63, 01/01/2018, 1
Type D, 256636, 01/01/2018, 1
Type A, 98798, 08/01/2018, 2
Type B, 34235, 08/01/2018, 2
Type C, 25676, 08/01/2018, 2
Type D, 74747, 08/01/2018, 2
Type A, 2525, 15/01/2018, 3
Type B, 63678, 15/01/2018, 3
Type C, 856, 15/01/2018, 3
Type D, 5799, 15/01/2018, 3
Type A, 54574, 23/01/2018, 4
Type B, 848989, 23/01/2018, 4
Type C, 3633, 23/01/2018, 4
Type D, 4367, 23/01/2018, 4
];
Left Join (Table)
LOAD Max(WeekSerial) as Max,
Max(WeekSerial, 2) as Max2
Resident Table;
AggrTable:
LOAD CUSTOMER_TYPE,
Sum(If(WeekSerial = Max, INVOICE_AMOUNT)) as CURRENT_WEEK,
Sum(If(WeekSerial = Max2, INVOICE_AMOUNT)) as PREVIOUS_WEEK
Resident Table
Group By CUSTOMER_TYPE;
Happy Friday Sunny! Many Thanks exactly what I was looking for.