Renaming dynamic headers

Question

I have a dataset, where there are dynamic header, which changes every week(adds up every week). Considering the latest dated header, how can I automatically use it for formula/filtering purpose.

Suppose the dataset has column:

ID             XY_0230

later next week header changes with new data as:

ID             XY_0310

In that case how can I operate logics where header is changing everytime?

danilang · Answer

Hi @ToxicBuoy (great name, btw)

If you know that it's always the last column that you need to use in a formula, who can use a technique like this

The Field Info tool extracts metadata, i.e.,  name, type, etc. from the input and returns it as a series of rows.  Take the last row with a sample tool and use a formula tool to create a static name for the last column.  Use Dynamic Rename to rename the last column to this static name.  Apply your formula or transformations using the static name.  When you've finished manipulating the data in the final column, use a second Dynamic Rename to restore it's original name.  Try adding another column to the input or removing the last one to see the effect.  Note that if you add or remove the last column, you'll get an error before you run the workflow, since Alteryx won't be able to figure out where the [UseInFormula] comes from.  Run the workflow and the error will disappear.

You can extend this technique to multiple columns if you can determine some way to filter for only those column names.

Dan

RenameLastColumn.yxmd

AngelosPachis · Answer

@ToxicBuoy  thank you for that, now it makes more sense.

For you to find the maximum date between your different columns, you can transpose your data to get all column headers in a single column. Then you can use a formula tool, to convert the column headers to a date format.

Subsequently with the summarize tool, you can find the maximum date in your dataset, and once you join it back to the original stream, the max date from your dataset will fall out of the J output anchor. This is where you can add a formula tool to create your new column, and then it's just a matter of bringing it all back together.

Testing

If my input is :

Then my new formula is applied in column XY_0201, concatenating the ID and the values in said column XY_0201

Now if I add another column in my input, XY_0208

then the formula has been applied in the newly introduced column

Hope that makes sense, it isn't the cleanest of ways but it appears to be working. Let me know if you have any questions.

Cheers,

Angelos

example.yxmd

ToxicBuoy · Answer

Hi @AngelosPachis ,

Lets say for example, I am having below columns in 1st week of Feb:

ID         XY_0111         XY_0118       XY_0125          XY_0201

so for 1st week analysis, I take latest week column i.e. XY_0201 and then I calculate other formula etc. like:

ID      XY_0201      (New Formula clumn based on XY_0201)

But in 2nd week of Feb, new column gets added like:

ID         XY_0111         XY_0118       XY_0125          XY_0201       XY_0208

In this case I am supposed to take XY_0208 for analysis,

But now since the header has changed from XY_0201 to XY_0208 (newest column), formula wont work on that,

hence I am looking to rename the latest column(here as XY_0208)

to something like XY_LATEST, so that which ever is the latest column in upcoming weeks, considers the latest column, renames it and formula can be applied always on it.