topic Re: Two tables in QlikView

Two tables

andreypico — Thu, 18 Apr 2013 14:49:57 GMT

Hi, all.

How make from table 1 table 2?

TABLE1

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

Thu, 18 Apr 2013 17:28:46 GMT

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 — Thu, 18 Apr 2013 20:32:52 GMT

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

andreypico — Fri, 19 Apr 2013 06:18:25 GMT

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

andreypico — Fri, 19 Apr 2013 06:27:17 GMT

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

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

When write-off -5 and -4 (KEY 2 and 4) from 10 (KEY 1) we will have 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