Looping in KDB /Q to assign a certain number of variables with irregular names and parameters-CodePudding

I have a set of variable assignments, shown below, that I need to generalize in a certain way.

bar1Q19:exec sum Quantity from table where date within (2019.01.01,2019.03.31)
bar2Q19:exec sum Quantity from table where date within (2019.04.01,2019.06.30)
...
bar3Q21:exec sum Quantity from table where date within (2021.07.01,2021.06.30)
bar4Q21:exec sum Quantity from table where date within (2021.10.01,2021.12.31)

I'm writing a script that will eventually be unsupervised, and at each quarter end will need to assign those variables for the past 8 quarters ONLY. I'm pretty new to KDB /Q so having to use over and scan instead of loops is quite confusing to me.

I was thinking structurally it might make sense to make an if statement to check what quarter the current month (which I have a variable for) is in and then use loops to work backward from that quarter, but it's much easier for me to describe than to actually implement. Any ideas on how to start would be greatly appreciated!

CodePudding user response：

To find the relevant dates for the last 8 quarters you could use this function:

q){reverse("d"$(-3*til y) m),'-1 "d"$(-3*-1 til y) m:3 xbar"m"$x}[.z.d;8]
2020.01.01 2020.03.31
2020.04.01 2020.06.30
2020.07.01 2020.09.30
2020.10.01 2020.12.31
2021.01.01 2021.03.31
2021.04.01 2021.06.30
2021.07.01 2021.09.30
2021.10.01 2021.12.31

where x is the current date and y is the number of quarters back you want to find.

Using this you can calculate the total Quantity for each quarter using a functional exec. A functional exec is useful here as we want to pass the list of dates as a variable to a function.

q)dts:{reverse("d"$(-3*til y) m),'-1 "d"$(-3*-1 til y) m:3 xbar"m"$x}[.z.d;8]
q)totals:{[r]?[table;enlist(within;`date;r);();(sum;`Quantity)]}each dts

If your database is partitioned you will need to use a different query as exec does not work on a partitioned database. Something like this should work:

totals:raze{exec Quantity from?[table;enlist(within;`date;x);0b;(enlist`Quantity)!enlist(sum;`Quantity)]}each dts

totals will be an 8 item list. From here you could use a dictionary to assign the bars, like so:

q)`bar1`bar2`bar3`bar4`bar5`bar6`bar7`bar8!totals