if else condition in script

[swatitomar]

Hi,

i have to write condition in script : 

if record>3 for max year , then show max year score

if record ❤️ for max year, then show previous year score.

and i have create the logic on max year data only

Ex:

ID, Year, Record, Score

P2, 2019, 2, 5.3

P2, 2018, 3, 2.4

P1, 2019, 5, 2.9

P3, 2019, 4, 3.4

P3, 2018, 6,2.0

P4, 2018, 8, 1.9

result : 2019

ID Total score
P1        2.9
P2        2.4
P3        3.4

PFA appl.

Thanks in Advance

[sunny_talwar]

Try this

Temp:
LOAD * INLINE [
    ID, Year, Record, Score
    P2, 2019, 2, 5.3
    P2, 2018, 3, 2.4
    P1, 2019, 5, 2.9
    P3, 2019, 4, 3.4
    P3, 2018, 6, 2.0
    P4, 2018, 8, 1.9
];

Final:
LOAD *,
	 If(Year = 2019 and Record >= 3, Score ,// 2019 score
	 If(Year = 2019 and Record < 3, Previous(Score)))// 2018 score
     as Total_score
Resident Temp
Order By ID, Year;
 
DROP table Temp ;