Can I move a formula in the list of "Group by" actions?

DHo

I've wondered this for a long time, but I'm always in a rush and end up using a select columns tile. That's inefficient, so I thought I'd ask …

Here's the scenario:

I've created a bunch of columns in my "group by" tile that spit out columns. I realize later that I used a basic "add column" function when I really needed a formula. I delete the "add column" version and add my formula, which is now the last column, but I want it back whereI had it originally.

That or any other reason you might change your mind about the order of columns in a group by tile.

Is the answer really "get it right the first time or use a select columns tile"?

rco

It would definitely be an improvement to be able to reorder aggregate columns in Group By. Column order as a whole is not given the attention it deserves in Magic ETL.

This workaround may be unwelcome; in most cases this will be more of a pain than just redoing the aggregates from the point where you want to reorder them. But in extreme cases where you have a very large number of aggregates, this is probably quicker:

1. Drag-select the Group By and at least one other tile to bring up the multi-tile control sidebar.

2. Click "Copy to Clipboard".

3. Paste into a text editor (ideally a code editor). This is the JSON definition of the copied tiles. Unfortunately, it is compacted; it's all on one line and has no indentation. A code editor will probably be able to reformat it for you so it's readable. Barring that you can use a JSON formatter website to do the same, but I'd be cautious of these if there is anything sensitive in the formulas themselves (not likely but possible).

4. You're looking for the Group By "fields" part of the JSON:

5. You can now use cut and paste to reorder these fields. Make sure to keep the JSON valid (commas must separate the fields and a comma after the final item in the array is not allowed).

6. When you're satisfied with the order, copy the entire document to your clipboard and paste it back into the Magic editor.

This is in no way a good solution for your problem, but I figure there's a small chance it's helpful in the future if you find yourself in a situation where you need to reorder a single aggregate in a list of dozens, or if you ever need to do something else that our editor won't let you do.

ggenovese

There's no way to reorder the columns in the UI as far as I know. This would be a great feature request though, either by allowing columns to be reordered like how the Add Formula tile works or by providing a method to convert an "Add Column" to be turned into an "Add Formula"

rco

It would definitely be an improvement to be able to reorder aggregate columns in Group By. Column order as a whole is not given the attention it deserves in Magic ETL.

This workaround may be unwelcome; in most cases this will be more of a pain than just redoing the aggregates from the point where you want to reorder them. But in extreme cases where you have a very large number of aggregates, this is probably quicker:

1. Drag-select the Group By and at least one other tile to bring up the multi-tile control sidebar.

2. Click "Copy to Clipboard".

3. Paste into a text editor (ideally a code editor). This is the JSON definition of the copied tiles. Unfortunately, it is compacted; it's all on one line and has no indentation. A code editor will probably be able to reformat it for you so it's readable. Barring that you can use a JSON formatter website to do the same, but I'd be cautious of these if there is anything sensitive in the formulas themselves (not likely but possible).

4. You're looking for the Group By "fields" part of the JSON:

5. You can now use cut and paste to reorder these fields. Make sure to keep the JSON valid (commas must separate the fields and a comma after the final item in the array is not allowed).

6. When you're satisfied with the order, copy the entire document to your clipboard and paste it back into the Magic editor.

This is in no way a good solution for your problem, but I figure there's a small chance it's helpful in the future if you find yourself in a situation where you need to reorder a single aggregate in a list of dozens, or if you ever need to do something else that our editor won't let you do.

DHo

Thanks @rco ! I didn't realize that was a thing. I might just use that on a couple of old flows to remove some column select tiles 😎