Show list of first/last values per date

[#1]

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

[#2]

may be like attached?

[its_anandrjs · Accepted solution]

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

[#4]

Works great, thank you Anand!!

[#5]

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


(biff, embedded labels, table is [Transaction log$]);

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$];

[its_anandrjs]

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

(biff, embedded labels, table is [Transaction log$]);

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

[#7]

Hero! Thank you very much Anand!