topic Get last balance in QlikView

Get last balance

Tue, 03 Aug 2010 17:00:19 GMT

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

Get last balance

suniljain — Tue, 03 Aug 2010 17:07:48 GMT

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.

Get last balance

Tue, 03 Aug 2010 17:39:21 GMT

Hello Sunil

Get last balance

Tue, 03 Aug 2010 17:43:51 GMT

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

Get last balance

suniljain — Tue, 03 Aug 2010 17:46:40 GMT

pls post your application.

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

Get last balance

Tue, 03 Aug 2010 17:52:14 GMT

Attached. Thanks for your help Sunil.

Get last balance

Tue, 03 Aug 2010 17:56:17 GMT

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;

Get last balance

Tue, 03 Aug 2010 18:10:06 GMT

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

Get last balance

Tue, 03 Aug 2010 18:37:50 GMT

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;

Get last balance

Tue, 03 Aug 2010 18:45:47 GMT

Your the man - work great! Thanks Eduardo.

Stephen

Get last balance

Tue, 03 Aug 2010 19:17:19 GMT

One last task, I need to fill in the "missing" dates with the Quantity from the date the part last had a balance so I can graph quantity on hand each day.

So if the quantity on hand for part A on 8/4/2010 is 7 and the next date that the quatity changes in 8/15/2010 to 20. I need to fill in the dates from 8/5/2010 to 8/14/2010 with a quantity of 7 for part A.

Help!

Thanks,

Stephen

Get last balance

Tue, 03 Aug 2010 20:49:27 GMT

Hi,

This code is not completed, but is the way... I try to complete the informatiom using the function peek(), but only one record was completed.

So... I stoped here...

I Create a table with the dates was missing, and join with the previous table.

ORIGINAL:
LOAD DATE(Date, 'DD/MM/YYYY') AS 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;

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

INNER JOIN (FINAL)
LOAD *
RESIDENT AUX_TABLE;

T:
LOAD
min(Date) as MinDate,
max(Date) as MaxDate
RESIDENT FINAL;

LET Start = peek('MinDate');
LET End = peek('MaxDate');
LET NumOfDays = End - Start + 1;

DATA:
JOIN (FINAL)
LOAD
$(Start) + Rowno() -1 as Date
AUTOGENERATE $(NumOfDays);

TESTE:
LOAD
Date,
Part,
IF(ISNULL(Part) = -1, PEEK(Part), Part) AS Part_,
Transaction,
Qty,
Balance
RESIDENT FINAL;

DROP TABLE T;
DROP TABLE FINAL;
DROP TABLE AUX_TABLE;
DROP TABLE ORIGINAL;

Get last balance

Tue, 03 Aug 2010 23:30:11 GMT

Still can't get it to work. Anyone?

Thanks,
Stephen

Get last balance

suniljain — Wed, 04 Aug 2010 09:20:07 GMT

Stephen,

I solved Your Problem.

Regards

Sunil Jain.

Get last balance

Wed, 04 Aug 2010 15:25:55 GMT

Thanks Sunil. I have one more task I must accomplish. Add a transaction for Part "A" on 8/8/2010 to add (Reciept) a quantity of 10. So now on the 8th the quantity for part A is 17. I need to add the dates of 8/5, 8/6/ and 8/7 and show a quantity of 7 for part A.

How would I do this?

Thanks,
Stephen

Get last balance

suniljain — Wed, 04 Aug 2010 17:12:51 GMT

Stephen,

No of record does not make any sense for this logic . pls check this by adding that records

Regards

Sunil.

Get last balance

Wed, 04 Aug 2010 17:42:18 GMT

Sunil,

I have attached a new Excel file. We want to graph "all" days. Meaning, for part A I want "all" days from 6/15/2010 to 7/17/2010. Not just the days for which the balance changed based on transaction for the day. Your code is gives me the ending balance for any day that has activity (thank you). But I need to fill in the "no activity" dates with the ending balance from the last date with activity. So, In the excel file on row 9 for 7/4/2010 the ending balance is 7. The next transaction (activity) date is 7/17/2010. I need to add the dates of 7/5, 7/6, 7/7 . . . . . to 7/16 and show that part A has a balance of 7.

We want to graph all days of the month and show the balance on hand for each date (regardless of whether it has activity). I can create a "master" calendar with all of the dates, but I don't know how to fill in the "no activity" dates with the part number ("A") and the last ending balance.

Hope this help explain what I am trying to accomplish.

Thanks,

Stephen

Get last balance

suniljain — Thu, 05 Aug 2010 12:28:31 GMT

remove group by statement from script . it will solved your problem.

Regards

Sunil

Get last balance

suniljain — Thu, 05 Aug 2010 12:38:50 GMT

pls remove group by from script.

it will solve your problem.

Regards;

Sunil