Two tables

[andreypico]

Hi, all.

How make from table 1 table 2?

TABLE1

Date	Qty	KEY
01.04.2013	10	1
02.04.2013	-5	2
03.04.2013	8	3
08.04.2013	-4	4
10.04.2013	-7	5

TABLE2

Date	Qty	Key	DateIn	QtyIn	KeyIn
01.04.2013	10	1	02.04.2013	-5	2
01.04.2013	5	1	08.04.2013	-4	4
01.04.2013	1	1	10.04.2013	-1	5
03.04.2013	8	3	10.04.2013	-6	5

[#2]

Hi ,

       Try the Below code

Tab:

LOAD * Inline

[

Date,          Qty,          Key,          DateIn,          QtyIn,          KeyIn

01.04.2013,          10,          1,          02.04.2013,          -5,          2

01.04.2013,          5,          1,          08.04.2013,          -4,          4

01.04.2013,          1,          1,          10.04.2013,          -1,          5

03.04.2013,          8,          3,          10.04.2013          ,-6,          5

];

NoConcatenate

Result:

LOAD  FirstValue(Date) as Date ,FirstValue(Key)as Key ,sum(Qty) as Qty Resident Tab Group by Key;

LOAD  FirstValue(DateIn) as Date , FirstValue(KeyIn) as Key , sum(QtyIn) as Qty Resident Tab Group by KeyIn;

DROP Table Tab;

Regards

Yusuf

[chematos]

Could you explain the relationship and the logic to get Table2 from Table1??

[andreypico]

You get table 2 from table 1. That's not what I wanted.

[andreypico]

I'm trying to create the FIFO method to write-off the remnants.

Qty KEY 1 write-off Qty KEY 2,4,5

Date	Qty	KEY
02.04.2013	-5	2
08.04.2013	-4	4
10.04.2013	-7	5

When write-off -5 and  -4 (KEY 2 and 4) from 10 (KEY 1) we will have 1.

Date	Qty	Key	DateIn	QtyIn	KeyIn
01.04.2013	10	1	02.04.2013	-5	2
01.04.2013	5	1	08.04.2013	-4	4
01.04.2013	1	1

1 we write-off part of -7 (KEY 5)

Date	Qty	Key	DateIn	QtyIn	KeyIn
01.04.2013	10	1	02.04.2013	-5	2
01.04.2013	5	1	08.04.2013	-4	4
01.04.2013	1	1	10.04.2013	-1	5

In result we have:

Date	Qty	Key	DateIn	QtyIn	KeyIn
01.04.2013	10	1	02.04.2013	-5	2
01.04.2013	5	1	08.04.2013	-4	4
01.04.2013	1	1	10.04.2013	-1	5
03.04.2013	8	3	10.04.2013	-6	5