get next values if NULL

[Anonymous]

Hi all,

Table 1:

EMPLID	STRM	GPA
1	1520	0.000
1	1530	3.750
1	1610	3.940
1	1620	3.940
1	1630	4.040
1	1710	4.040
1	1810	3.400
1	1820	3.400
1	1830	NULL

This will be done in the loading script. What i want to be able to do is to get the GPA from MAX(STRM)  if it is not null.

Let me elaborate. If there is a value in 1830 , then pick up the GPA from there.

In this scenario, where the GPA in the highest STRM 1830 is null, i want to be able to get the GPA of 3.400 from STRM 1820.

Desired Output:

EMPLID      STRM      GPA

1                   1820       3.4

Any form of help is greatly appreciated.

Thank you for your time!

[thannila]

Data:

load * Inline

[

EMPLID,STRM,GPA

1,1520,0.000

1,1530,3.750

1,1610,3.940

1,1620,3.940

1,1630,4.040

1,1710,4.040

1,1810,3.400

1,1820,3.400

1,1830,NULL

];

right join(Data)

Data1:

load EMPLID,

max(STRM) AS STRM

RESIDENT Data where GPA<>'NULL' group by EMPLID;