latest

[#1]

Hello Comunity,

I need your help

Table1:

id	marks1	marks2
1	0	10
2	4	8
3	3	6
4	2	9
5	1	5

Table2:

id	total	date
1	10	1-Jan-16
2	3	3-Jan-16
2	3	4-Jan-16
2	3	2-Jan-16
3	4	10-Jan-16
3	4	11-Jan-16
4	4	14-Feb-16
4	5	15-Feb-16
4	4	16-Feb-16
5	5	1-Apr-16

My output should be like this

id	total	date	totalnew
1	10	1-Jan-16	10
2	3	3-Jan-16	3
2	3	4-Jan-16	3
2	3	2-Jan-16	2
3	4	10-Jan-16	4
3	4	11-Jan-16	2
4	5	15-Feb-16	5
4	4	16-Feb-16	4
5	5	1-Apr-16	5

consider marks2 is latest

Suppose,

For id 2 marks would be 8

Based on latest it should be from table2 3+3+2=8

Is it possible

Any suggestions please help!!!

[MarcoWedel]

please elaborate on your requirements

[sunny_talwar]

Not entirely sure if I understand your requirement either, but may be this:

Table1:

LOAD id,

    total,

    Date#(date, 'D-MMM-YY') as date

FROM

[https://community.qlik.com/thread/212731]

(html, codepage is 1252, embedded labels, table is @2);

Join(Table1)

LOAD id,

    marks1,

    marks2

FROM

[https://community.qlik.com/thread/212731]

(html, codepage is 1252, embedded labels, table is @1);

TempTable:

LOAD id,

  total,

  date,

  If(id = Peek('id'), RangeSum(Peek('NT'), total), total) as NT,

  If(marks2 - If(id = Peek('id'), RangeSum(Peek('NT'), total), total) >= 0, total, total + marks2 - If(id = Peek('id'), RangeSum(Peek('NT'), total), total)) as NT3

Resident Table1

Order By id, date;

FinalTable:

LOAD id,

  total,

  date,

  NT3 as totalnew

Resident TempTable

Where NT3 > 0;

DROP Table Table1, TempTable;

[#4]

Thanks for your effort,

Almost there but here it should be like this

For id4, in table1 we have 9 so based on the  latest date it should be 4+5=9.

id total date totalnew datenew

1  10  1-Jan-16    10   1-Jan-16

2   3   3-Jan-16      3    3-Jan-16

2   3   4-Jan-16      3    4-Jan-16

2   3   2-Jan-16      2    2-Jan-16

3   4   10-Jan-16    4    10-Jan-16

3   4   11-Jan-16    2    11-Jan-16

4   4   14-Feb-1     4      14-Feb-16

4   5   15-Feb-16   5    15-Feb-16--> should be reflect based on the latest date2 for id4

4   4   16-Feb-16   4    16-Feb-16--> should be reflect based on the latest date1 for id4

5   5   1-Apr-16     5    1-Apr-16

Is it possible??

Pls help

[sunny_talwar · Accepted solution]

Try this:

Table1:

LOAD id,

    total,

    Date#(date, 'D-MMM-YY') as date

FROM

[https://community.qlik.com/thread/212731]

(html, codepage is 1252, embedded labels, table is @2);

Join(Table1)

LOAD id,

    marks1,

    marks2

FROM

[https://community.qlik.com/thread/212731]

(html, codepage is 1252, embedded labels, table is @1);

TempTable:

LOAD id,

  total,

  date,

  If(id = Peek('id'), RangeSum(Peek('NT'), total), total) as NT,

  If(marks2 - If(id = Peek('id'), RangeSum(Peek('NT'), total), total) >= 0, total, total + marks2 - If(id = Peek('id'), RangeSum(Peek('NT'), total), total)) as NT3

Resident Table1

Order By id, date desc;

FinalTable:

LOAD id,

  total,

  date,

  NT3 as totalnew

Resident TempTable

Where NT3 > 0;

DROP Table Table1, TempTable;