Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
andreypico
Contributor III
Contributor III

Two tables

Hi, all.

How make from table 1 table 2?

TABLE1

Date
Qty
KEY
01.04.2013101
02.04.2013-52
03.04.201383
08.04.2013-44
10.04.2013-75

TABLE2

DateQtyKeyDateInQtyInKeyIn
01.04.201310102.04.2013-52
01.04.20135108.04.2013-44
01.04.20131110.04.2013-15
03.04.20138310.04.2013-65
4 Replies
Not applicable

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
Specialist II
Specialist II

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

andreypico
Contributor III
Contributor III
Author

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

andreypico
Contributor III
Contributor III
Author

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-52
08.04.2013-44
10.04.2013-75

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

DateQtyKeyDateInQtyInKeyIn
01.04.201310102.04.2013-52
01.04.20135108.04.2013-44
01.04.201311

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

DateQtyKeyDateInQtyInKeyIn
01.04.201310102.04.2013-52
01.04.20135108.04.2013-44
01.04.20131110.04.2013-15

In result we have:

DateQtyKeyDateInQtyInKeyIn
01.04.201310102.04.2013-52
01.04.20135108.04.2013-44
01.04.20131110.04.2013-15
03.04.20138310.04.2013-65