3 Replies Latest reply: Oct 24, 2017 6:31 AM by dave liu RSS

    Replace Nesting Ifs

    Matias Eiletz

      Hello to all,

       

      I have a field that has dates in different formats, called "Completion Week".

      From that field, I want to make another field called "Year", where it just shows the year of the date.

       

      So the "problematic" format, is the one that shows the date in weeks without expressing the year, for example: 1022-1028.

      The weeks always go from Sunday to Saturday. So in this case, the year is 2017.

       

      The rule that I made in the following lines, checks if the first part of that string ('1022', in the previous case) corresponds to a Sunday in the current year. If it isn't, check the same for 2016. If it isn't, checks the same for 2015, and so on, until finding a year where this condition is reached, and assigns that Year for that week.


      The code works fine, but the problem is that I find it too long and not proper.


      ZZZZZZZZZZZZCaptuWre.PNG

       

      I would like to know if there's a more efficient way to carry out this script. Some piece of code with less lines, so that is more efficient and proper.

       

      I appreciate any piece of advice.

       

      Thank you!

        • Re: Replace Nesting Ifs
          dave liu

          Hi

          can you provide all kind of date formate of 'Completion Week' and the Year they belong to?

            • Re: Replace Nesting Ifs
              Matias Eiletz

              The field 'Completion Week', has values like these ones. 

               

              0221-0227
              0228-0305
              0306-0312
              0312-0318
              0313-0319
              0320-0326
              0326-0301
              0326-0401
              0327-0402
              0403-0409
              0409-0415
              0410-0416
              0416-0422

               

              I would like to assign the Year for each week, knowing that the first part of the string before the "-", is a Sunday (MMDD).

               

              Please ignore the first line of the nested ifs in the image I posted before. That condition is for another thing.

                • Re: Replace Nesting Ifs
                  dave liu

                  is this better ?

                   

                  For i = 2017 to 2009 step -1

                  LOAD * Where Year <> Null() and Not Exists([Completion Week]);

                  LOAD

                  [Completion Week],

                  IF(WeekDay(MakeDate($(i),Left([Completion Week],2),Right(Left([Completion Week],4),2)))='Sun',$(i)) as Year

                   

                  Inline [

                  Completion Week

                  0221-0227

                  0228-0305

                  0306-0312

                  0312-0318

                  0313-0319

                  0320-0326

                  0326-0301

                  0326-0401

                  0327-0402

                  0403-0409

                  0409-0415

                  0410-0416

                  0416-0422

                  ];

                  Next