Fortnight sequence in loadscript

[#1]

Hi,

In load script, how can we generate the column of "fortnight" which should contain a sequence numbers for entire year (i.e.Jan 2014 till Jan 14 fortnight value should be 1 and 2 should be for till Jan 31 2015)and same sequence should repeat for other years.

Ex:

Dates,             Fortnight

2014 Jan 1 -14 , 1

2014 Jan 15- 31, 2

2014 Feb 1-14,   3

2014 Feb 15-28, 4

.

.

.

2014 Dec 15-31, 24

2015 Jan  1-14, 1

2015 Jan 15-31, 2

.

.

.

What was the calculated formula in load script ?

Thanks in advance.

Regards,

Satish A.

[sunny_talwar]

Try this:

If(Peek('Year') = Year, Alt(Peek('Fortnight'), 1), 1) as Fortnight

[sunny_talwar]

Misread your requirement. Do you have all the dates between Jan 1st and Jan 15th and all need to have 1 value for Fortnight?

[Ralf-Narfeldt]

Do you have a master calendar?

You could add this expression for Fortnight:

If(Day(date_field)<15, Month(date_field)*2-1, Month(date_field)*2)

[sunny_talwar]

Awesome.

Didn't think it is going to be this easy as I have never dealt with Fortnight.

Thanks for sharing

[Ralf-Narfeldt · Accepted solution]

Neither have I. My first instinct would be to use the Week number divide by two and rounded up, but this example is based on months, so the second fortnight of a month is always the remainder of month.

[#7]

Hi Ralf,

Excellent, this was exactly i looked for.

Thank you...

Regards,

Satish A.