Please see attached image and file. After a few gazzilian calculations and groupings I have two months’ with no data in a category. The data is grouped by each year, then each month in each year. In the image below and excel file attached, I have two months with zero data (Jan 2010 and Feb 2011). It’s not my typical problem of a blank field or two that can be fixed with the Missing Value Node. In this case, since there is zero data for these two months, there’s simply no row in the table or empty field.
How do I enforce the requirement there must be: 1) every year within a range and 2) twelve months required for every year in the range? Or perhaps better, force the missing months to appear in the table and populate the fields with all zeros for each column?
Maybe something with the Rule Engine? I just don’t know exactly how to first require the “every year and 12 months” rule. Maybe there’s something that can be done with a date&time node?
Hi @smithcreed , the cleanest way is probably doing a left join with a table containing all 12 months for the required years, that way the final results will contain all the 12 months for the required years.
To create that, you can do a Cross join with the required years and with the 12 months, and to get the required year, you can just get the unique years by using the GroupBy node.
Putting everything together, something like this will do:
@bruno29a , so wow, your solution is definitely not something I was likely to arrive at on my own for some time. Your Table Creator node is essentially the “Dictionary” for this solution. I’ve used the dictionary function before, but for converting many random words with similar meanings to a specific, consistent word. But for some reason my brain was not making that connection in this context… Thanks for the solution! I will get this implemented in my workflow.
You can use “Date&time Range” node to create this dictionary to automate your process and flow, and then, you can use the @bruno29a example to do all the process… just change the table creator for it.
Hi @denisfi , normally I want to automate as much as possible and use dynamic values. But in some cases, it might be much simpler to use a static table.
In this case, that table creator contains the 12 months. Straight forward, something that does not change, only 12 records to copy (or you can even just copy the node). It’s a simple one node.
To reproduce this with the Date&Time Range node, you have to first configure the node, and then use the Extract Date&Time Fields node.
@bruno29a@denisfi Thank you both for the advice. I implemented the original solution and resolved my issues. I will also keep in mind the slightly more complex automated solution when this kind of issue again arises. Thanks again!
You can use de date&time range to set the current date (today) as system default, after that, you can set a date diff to set 1 year or month to calculate ant put it on a variable, then you can set date to string to adjust for 01 day (first date for that month)… it will automaticaly set the today and 1 year before… then you can put these variabel again to a data range note to build all the 12 months…
Other way is get the today date, use the extract date node to get the year only and then create the first date to build all the range for the year with da date&time range again.
Just for automate the date for flow… very useful who works with dynamics dates.
Hey @denisfi , yes I know how to use the Date&Time Range node
But you’ve just proven my point by describing the several steps that need to be done, and as you said it “very useful who works with dynamics dates”, and I agree. In this case, it does not need to be dynamic. Having a hardcoded table for 12 months from January to December will never change, and does not need to be dynamic, and does not need the additional operations (and additional system resource utilization) to compute these values dynamically. Whether I run it today, tomorrow, in a week, in a few months, in a year, these values do not need to be re-computed. For “preparation”, the static table is just a copy and paste from a few lines from the Excel sheet itself. For execution, it’s just reading the node (12 lines, nothing to compute).
The Date&Time Range node is a very useful tool, but it does not mean that we have to always use it if it’s not needed. Just like a hammer is a very useful tool for hitting things, but if I can press a pin into a cardboard with my finger, I am not going to go look for a hammer
Sure, but i just give a point of view that you can set it and forget forever… change the year/month, the dinamic dates will be up to date… just it… I agree about the static table for this case.
