topic Re: latest in QlikView

latest

Wed, 25 Nov 2020 16:16:04 GMT

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!!!

Re: latest

MarcoWedel — Sun, 10 Apr 2016 20:02:58 GMT

please elaborate on your requirements

Re: latest

sunny_talwar — Sun, 10 Apr 2016 21:49:21 GMT

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;

Re: latest

Mon, 11 Apr 2016 08:11:05 GMT

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

Re: latest

sunny_talwar — Mon, 11 Apr 2016 09:31:49 GMT

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;