topic Re: Fortnight sequence in loadscript in QlikView

Fortnight sequence in loadscript

Mon, 14 Sep 2015 05:12:20 GMT

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.

Re: Fortnight sequence in loadscript

sunny_talwar — Mon, 14 Sep 2015 05:15:19 GMT

Try this:

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

Re: Fortnight sequence in loadscript

sunny_talwar — Mon, 14 Sep 2015 05:24:01 GMT

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

Re: Fortnight sequence in loadscript

Ralf-Narfeldt — Mon, 14 Sep 2015 05:26:40 GMT

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)

Re: Fortnight sequence in loadscript

sunny_talwar — Mon, 14 Sep 2015 05:31:23 GMT

Awesome.

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

Thanks for sharing

Re: Fortnight sequence in loadscript

Ralf-Narfeldt — Mon, 14 Sep 2015 05:35:40 GMT

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.

Re: Fortnight sequence in loadscript

Mon, 14 Sep 2015 05:44:04 GMT

Hi Ralf,

Excellent, this was exactly i looked for.

Thank you...

Regards,

Satish A.