7 Replies Latest reply: Mar 9, 2016 10:48 AM by Andrzej Bejmart RSS

    Find the latest Year "used"

    Andrzej Bejmart

      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

       

         

      OptionYear CAPEX
      PRJ_0012018521
      PRJ_0012019260
      PRJ_0012020312
      PRJ_00120212605
      PRJ_00120221563
      PRJ_00120230
      PRJ_00120240
      PRJ_00120250
      PRJ_00120260
      PRJ_00120270
      PRJ_00120280
      PRJ_00120290
      PRJ_00120300
      PRJ_0022014582
      PRJ_00220152330
      PRJ_00220162330
      PRJ_0022017582
      PRJ_00220180
      PRJ_00220190
      PRJ_00220200
      PRJ_0022021582
      PRJ_00220220
      PRJ_00220230
      PRJ_00220240
      PRJ_00220250
      PRJ_00220260
      PRJ_00220270
      PRJ_00220280
      PRJ_00220290
      PRJ_00220300

       

       

      Thank you

        • Re: Find the latest Year "used"
          Saran De

          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

          ;

          • Re: Find the latest Year "used"
            Kaushik Solanki

            HI,

             

            You can use the where condition like below.

             

            Load *

            from xyz where Capex <> 0;

             

            Regards,

            Kaushik Solanki

            • Re: Find the latest Year "used"
              Andrzej Bejmart

              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:

               

              OptionYearCAPEXFinal Year
              PRJ_00120185212022
              PRJ_00120192602022
              PRJ_00120203122022
              PRJ_001202126052022
              PRJ_001202215632022
              PRJ_001202302022
              PRJ_001202402022
              PRJ_001202502022
              PRJ_001202602022
              PRJ_001202702022
              PRJ_001202802022
              PRJ_001202902022
              PRJ_001203002022
              PRJ_00220145822021
              PRJ_002201523302021
              PRJ_002201623302021
              PRJ_00220175822021
              PRJ_002201802021
              PRJ_002201902021
              PRJ_002202002021
              PRJ_00220215822021
              PRJ_002202202021
              PRJ_002202302021
              PRJ_002202402021
              PRJ_002202502021
              PRJ_002202602021
              PRJ_002202702021
              PRJ_002202802021
              PRJ_002202902021
              PRJ_002203002021
                • Re: Find the latest Year "used"
                  Kaushik Solanki

                  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

                • Re: Find the latest Year "used"
                  Saran De

                  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.