Find the latest Year "used"

[#1]

Hi guys,

I have table with 3 fields Project Number, Year, CAPEX. I need to find in which Year there is a CAPEX in each project. I need to do it within the script.

Here is sample data

Option	Year	CAPEX
PRJ_001	2018	521
PRJ_001	2019	260
PRJ_001	2020	312
PRJ_001	2021	2605
PRJ_001	2022	1563
PRJ_001	2023	0
PRJ_001	2024	0
PRJ_001	2025	0
PRJ_001	2026	0
PRJ_001	2027	0
PRJ_001	2028	0
PRJ_001	2029	0
PRJ_001	2030	0
PRJ_002	2014	582
PRJ_002	2015	2330
PRJ_002	2016	2330
PRJ_002	2017	582
PRJ_002	2018	0
PRJ_002	2019	0
PRJ_002	2020	0
PRJ_002	2021	582
PRJ_002	2022	0
PRJ_002	2023	0
PRJ_002	2024	0
PRJ_002	2025	0
PRJ_002	2026	0
PRJ_002	2027	0
PRJ_002	2028	0
PRJ_002	2029	0
PRJ_002	2030	0

Thank you

[Saravanan_Desingh]

LOAD * INLINE [

    Option, Year, CAPEX

    PRJ_001, 2018, 521

    PRJ_001, 2019, 260

    PRJ_001, 2020, 312

    PRJ_001, 2021, 2605

    PRJ_001, 2022, 1563

    PRJ_001, 2023, 0

    PRJ_001, 2024, 0

    PRJ_001, 2025, 0

    PRJ_001, 2026, 0

    PRJ_001, 2027, 0

    PRJ_001, 2028, 0

    PRJ_001, 2029, 0

    PRJ_001, 2030, 0

    PRJ_002, 2014, 582

    PRJ_002, 2015, 2330

    PRJ_002, 2016, 2330

    PRJ_002, 2017, 582

    PRJ_002, 2018, 0

    PRJ_002, 2019, 0

    PRJ_002, 2020, 0

    PRJ_002, 2021, 582

    PRJ_002, 2022, 0

    PRJ_002, 2023, 0

    PRJ_002, 2024, 0

    PRJ_002, 2025, 0

    PRJ_002, 2026, 0

    PRJ_002, 2027, 0

    PRJ_002, 2028, 0

    PRJ_002, 2029, 0

    PRJ_002, 2030, 0

]

Where CAPEX <> 0

;

[kaushiknsolanki]

HI,

You can use the where condition like below.

Load *

from xyz where Capex <> 0;

Regards,

Kaushik Solanki

[#4]

Probably I didn't explain well enough what I need.

I know hot to get reed of the 0 values. I need to know in which year we well make last payment.

something like that:

Option	Year	CAPEX	Final Year
PRJ_001	2018	521	2022
PRJ_001	2019	260	2022
PRJ_001	2020	312	2022
PRJ_001	2021	2605	2022
PRJ_001	2022	1563	2022
PRJ_001	2023	0	2022
PRJ_001	2024	0	2022
PRJ_001	2025	0	2022
PRJ_001	2026	0	2022
PRJ_001	2027	0	2022
PRJ_001	2028	0	2022
PRJ_001	2029	0	2022
PRJ_001	2030	0	2022
PRJ_002	2014	582	2021
PRJ_002	2015	2330	2021
PRJ_002	2016	2330	2021
PRJ_002	2017	582	2021
PRJ_002	2018	0	2021
PRJ_002	2019	0	2021
PRJ_002	2020	0	2021
PRJ_002	2021	582	2021
PRJ_002	2022	0	2021
PRJ_002	2023	0	2021
PRJ_002	2024	0	2021
PRJ_002	2025	0	2021
PRJ_002	2026	0	2021
PRJ_002	2027	0	2021
PRJ_002	2028	0	2021
PRJ_002	2029	0	2021
PRJ_002	2030	0	2021

[kaushiknsolanki · Accepted solution]

Find the logic below

Data:

LOAD * INLINE [

    Option, Year, CAPEX

    PRJ_001, 2018, 521

    PRJ_001, 2019, 260

    PRJ_001, 2020, 312

    PRJ_001, 2021, 2605

    PRJ_001, 2022, 1563

    PRJ_001, 2023, 0

    PRJ_001, 2024, 0

    PRJ_001, 2025, 0

    PRJ_001, 2026, 0

    PRJ_001, 2027, 0

    PRJ_001, 2028, 0

    PRJ_001, 2029, 0

    PRJ_001, 2030, 0

    PRJ_002, 2014, 582

    PRJ_002, 2015, 2330

    PRJ_002, 2016, 2330

    PRJ_002, 2017, 582

    PRJ_002, 2018, 0

    PRJ_002, 2019, 0

    PRJ_002, 2020, 0

    PRJ_002, 2021, 582

    PRJ_002, 2022, 0

    PRJ_002, 2023, 0

    PRJ_002, 2024, 0

    PRJ_002, 2025, 0

    PRJ_002, 2026, 0

    PRJ_002, 2027, 0

    PRJ_002, 2028, 0

    PRJ_002, 2029, 0

    PRJ_002, 2030, 0

]

;

Left Join

Load Option, Max(Year) as LastYear

Resident Data where CAPEX <> 0

group by Option;

Regards,

Kaushik Solanki

[#6]

This just found max value in the year Field (2030) and paste it as the last year to every project...

I tried to play around with where condition, but no success.

[Saravanan_Desingh]

Hi Andrzej, Kaushik's solution is working. Please find the attachment. He added Group By.

If you are looking for something different, please let us know.

[#8]

Thank you, will need to test it more and find why it doesn't wont to work in real life data.