Get last balance

[#1]

I have a table the shows transactions for parts with a running balance. I need to get the part and the "last" balance. For example the first table looks like :

Date Part Tranaction Qty Balance

8/32010 A Reciept 10 10

8/3/2010 A Issued 2 8

8/3/2010 A Sold 5 3

8/3/2010 B Reciept 40 40

8/3/2010 B Sold 25 15

I need to create a table that only has the last balance. So I want:

8/3/2010 A 3

8/3/2010 B 15

How can I get this?

Thanks,

Stephen

[suniljain]

Dear Stephen

Try This One

X:
LOAD Date,
Part,
Tranaction ,
if(Transaction = 'Receipt',Qty,Qty*-1) as Qty
FROM
Stock.xls(biff, embedded labels, table is Sheet1$);

Y:
load *,recno() as d resident X order by Product,Date;
drop table X;
//exit script;
Main:
Load
Part,
Date,
Qty,

if (peek(Part)=Part,peek(ClosingQty)+Qty,Qty) as ClosingQty
resident Y order by d;
drop field d;
drop table Y;

Regards

Sunil Jain.

[#3]

Hello Sunil

[#4]

Hello Sunil,

I tried your script and if I don't drop your field "d" and I create a straight table using Date and Part as dimensions and I create a expression Max(d) I do get the pointer the ClosingQty I want but I cant seem to get an expression to get the value to work.

Any ideas?

Thanks,

Stephen

[suniljain]

pls post your application.

I will send you that application after solving error in it.

[#6]

Attached. Thanks for your help Sunil.

[#7]

TEST:
LOAD
RowNo() AS Id,
Date,
Part,
Tranaction,
Qty,
Balance
INLINE [
Date, Part, Tranaction, Qty, Balance
8/3/2010, A, Reciept, 10, 10
8/3/2010, A, Issued, 2, 8
8/3/2010, A, Sold, 5, 3
8/3/2010, B, Reciept, 40, 40
8/3/2010, B, Sold, 25, 15
];

TEST1:
LOAD
MAX(Id) AS Id,
Part
RESIDENT TEST
GROUP BY Part;

FINAL:
INNER JOIN (TEST1)
LOAD *
RESIDENT TEST;

DROP TABLE TEST;

[#8]

Eduardo,

INLINE [
Date, Part, Tranaction, Qty, Balance
8/3/2010, A, Reciept, 10, 10
8/3/2010, A, Issued, 2, 8
8/3/2010, A, Sold, 5, 3

8/4/2010, A, Receipt, 10, 13

8/4/2010,A,Sold,7,6

The table I have has many dates for each part and I need the ending balance for each date.

Stephen

[#9]

Hi sjcharles,

maybe this resolve your problem.

ORIGINAL:
LOAD Date,
Part,
Transaction,
Qty,
Balance
FROM
C:\Users\ecorrea.SERVER\Desktop\GetLastQty.xls
(biff, embedded labels, table is Sheet1$);

AUX_TABLE:
LOAD
Date,
Part,
Date & Part AS KEY,
Transaction,
Qty,
Balance,
RowNo() AS Id
RESIDENT ORIGINAL;

TEMP:
LOAD
MAX(Id) AS Id,
Date & Part AS KEY
RESIDENT AUX_TABLE
GROUP BY Date, Part;

FINAL:
INNER JOIN (TEMP)
LOAD *
RESIDENT AUX_TABLE;

DROP TABLE AUX_TABLE;
DROP TABLE ORIGINAL;

[#10]

Your the man - work great! Thanks Eduardo.

Stephen