Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I've been trying to present a list of start and end balances (separately).
The fist value will always correspond to SettlementOrder=1 but the last one varies every day.
Something like the below:
Start Balances
Date SettlementOrder Balance
20/06/2014 1 845
21/06/2014 1 10958
22/06/2014 1 70135
End Balances
Date SettlementOrder Balance
20/06/2014 12 6404
21/06/2014 9 63570
22/06/2014 8 748023
Any ideas?
Kind Regards,
Olle
Try to load your table like below load script and then create two tables one for Start Balances and another one for End Balances
LOAD Date,
SettlementOrder,
Transaction,
Balance
FROM
FirstLastExample.xlsx
(ooxml, embedded labels);
StartBalances:
LOAD Date&'_'&StartBalance as Key1,Date,StartBalance;
LOAD
Date,
FirstSortedValue(Balance,SettlementOrder) as StartBalance
Resident Sheet1
Group By Date;
Left Join
LOAD
Date(Date,'DD/MM/YYYY')&'_'&Balance as Key1,
SettlementOrder as StartOrder
Resident Sheet1;
EndBalances:
LOAD Date&'_'&EndBalance as Key2,Date,EndBalance;
LOAD
Date,
FirstSortedValue(Balance,-SettlementOrder) as EndBalance
Resident Sheet1
Group By Date;
Left Join
LOAD
Date(Date,'DD/MM/YYYY')&'_'&Balance as Key2,
SettlementOrder as EndOrder
Resident Sheet1;
And then you get two separate tables one for Start and another one for End balances.
Regards
may be like attached?
Try to load your table like below load script and then create two tables one for Start Balances and another one for End Balances
LOAD Date,
SettlementOrder,
Transaction,
Balance
FROM
FirstLastExample.xlsx
(ooxml, embedded labels);
StartBalances:
LOAD Date&'_'&StartBalance as Key1,Date,StartBalance;
LOAD
Date,
FirstSortedValue(Balance,SettlementOrder) as StartBalance
Resident Sheet1
Group By Date;
Left Join
LOAD
Date(Date,'DD/MM/YYYY')&'_'&Balance as Key1,
SettlementOrder as StartOrder
Resident Sheet1;
EndBalances:
LOAD Date&'_'&EndBalance as Key2,Date,EndBalance;
LOAD
Date,
FirstSortedValue(Balance,-SettlementOrder) as EndBalance
Resident Sheet1
Group By Date;
Left Join
LOAD
Date(Date,'DD/MM/YYYY')&'_'&Balance as Key2,
SettlementOrder as EndOrder
Resident Sheet1;
And then you get two separate tables one for Start and another one for End balances.
Regards
Works great, thank you Anand!!
I got it to work in my example file but now that I try to apply it to my master file it says: Table Not Found, for some reason I do not understand.
Do you have any ideas?
Thanks in advance,
Olle
ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='£#,##0.00;-£#,##0.00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='YYYY/MM/DD';
SET TimestampFormat='YYYY/MM/DD hh:mm:ss[.fff]';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
ColumnHeader:
LOAD
[Running balance] as Balance,
[Settlement order] as SettlementOrder,
[Settlement Date] as Date,
[Settlement Time] as Time
FROM
(
StartBalances:
LOAD Date&'_'&StartBalance as Key1,Date,StartBalance;
LOAD
Date,
FirstSortedValue(Balance,SettlementOrder) as StartBalance
Resident [Transaction log$]
Group By Date;
Left Join
LOAD
Date(Date,'DD/MM/YYYY')&'_'&Balance as Key1,
SettlementOrder as StartOrder
Resident [Transaction log$];
EndBalances:
LOAD Date&'_'&EndBalance as Key2,Date,EndBalance;
LOAD
Date,
FirstSortedValue(Balance,-SettlementOrder) as EndBalance
Resident [Transaction log$]
Group By Date;
Left Join
LOAD
Date(Date,'DD/MM/YYYY')&'_'&Balance as Key2,
SettlementOrder as EndOrder
Resident [Transaction log$];
Oh the name of the base table that you use is ColumnHeader not [Transaction log$] then write script like if you have need to use same then rename that because resident table refers to the base table
ColumnHeader:
LOAD
[Running balance] as Balance,
[Settlement order] as SettlementOrder,
[Settlement Date] as Date,
[Settlement Time] as Time
FROM
(
StartBalances:
LOAD Date&'_'&StartBalance as Key1,Date,StartBalance;
LOAD
Date,
FirstSortedValue(Balance,SettlementOrder) as StartBalance
Resident ColumnHeader // Change this names
Group By Date;
Left Join
LOAD
Date(Date,'DD/MM/YYYY')&'_'&Balance as Key1,
SettlementOrder as StartOrder
Resident ColumnHeader; // Change this names
EndBalances:
LOAD Date&'_'&EndBalance as Key2,Date,EndBalance;
LOAD
Date,
FirstSortedValue(Balance,-SettlementOrder) as EndBalance
Resident ColumnHeader // Change this names
Group By Date;
Left Join
LOAD
Date(Date,'DD/MM/YYYY')&'_'&Balance as Key2,
SettlementOrder as EndOrder
Resident ColumnHeader; // Change this names
Hero! Thank you very much Anand!