Replace Nesting Ifs

[Anonymous]

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.

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!

[woshua5550]

Hi

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

[Anonymous]

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.

[woshua5550]

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