Calgary XML for dynamic date range

Question

Hey group. I have a workflow that pulls a Calgary table. I inherited it from a coworker, who is opening it monthly to select the date values needed (-24 months to present).  The date field is formatted YYYY-mm, and I'm trying to find a way to write code into it that would automatically change the query so it can be automated on the Alteryx server.

A few potential options I see:

1) Manual code in the XML query portion of the Calgary Input tool (and I am 100% unfamiliar with XML)

2) I've seen some things online about using a Calgary join, but am still working on finding resources to walk me through it

3) Use a Dynamic Input tool instead of the Calgary tools (though, since there's no query builder option for the Calgary table, I'm not sure this is possible)

Any thoughts on the easiest way to dynamically adjust the date range so I can automate this workflow? Thanks in advance.

MCDR929 · Answer

I ended up using the Calgary join tool, and that seems to work well in this case.

mceleavey · Answer

Hi @MCDR929 ,

You can wrap the calgary loader into a macro, and in the query sectionof the loader you can determine the range on the date field as such:

Then in your workflow you can dynamically determine the date range as, for example, from 4 weeks ago to today's date. You can then feed these values into the macro as control parameters to overwrite the xxxx and yyyy values. This will make your query dynamic.

Hope this helps.

M.