How to extract the last n days per month of a list of dates?
So let's say I have the following list of dates:
dates:2000.01.01 + til 60
and n:2.
The expected result I want is :
2000.01.30 2000.01.31 2000.02.28 2000.02.29
You want to build a dictionary of the months for each date and then group it.Once that is done you can use the # operator. Update: sublist possibly is a better operator, to handle possible duplicates, this depends on the incoming data
// Build the dictionary/mapping
q)dates!`month$dates
2000.01.01| 2000.01
2000.01.02| 2000.01
2000.01.03| 2000.01
2000.01.04| 2000.01
// Group the results
q)group dates!`month$dates
2000.01| 2000.01.01 2000.01.02 2000.01.03 2000.01.04 2000.01.05 2000.01.06 20..
2000.02| 2000.02.01 2000.02.02 2000.02.03 2000.02.04 2000.02.05 2000.02.06 20..
// Act on each month
q)-2#/:group dates!`month$dates
2000.01| 2000.01.30 2000.01.31
2000.02| 2000.02.28 2000.02.29
// Raze the results
q)raze value -2#/:group dates!`month$dates
2000.01.30 2000.01.31 2000.02.28 2000.02.29
So to summarize, you build a mapping of the dates to their respective months by casting the dates, then group them. Once that is done you can extract the results. You will want to make sure the dates are sorted before operating on them.
We can place this all into a nice function with the ominous name of lastDays
q)lastDays:{[dts;n] raze value (neg n)#/:group dts!`month$dts}
q)lastDays[dates;2]
2000.01.30 2000.01.31 2000.02.28 2000.02.29
Edited to leverage sublist instead of #, as per Terrys suggestion
q)lastDays:{[dts;n] raze value sublist/:[neg n;]group dts!`month$dts}
q)lastDays[dates;2]
2000.01.30 2000.01.31 2000.02.28 2000.02.29