Dynamic aggregation column name in Azure Data factory

Mikkel Treu Os 31

Hi,
@MartinJaffer-MSFT . I've seen you had some great answers to similar topics before, so linking this to you:-)

I have a dataflow which I want to keep as dynamic as possible.
To do this, I have defined an array parameter that is used to specify the key columns. These are used to specify the key columns in the SQL DB sink.
There can be one or more columns in this array.

However, to handle duplicates within one incremental load, I also need to remove duplicates based on the same parameter.
I.e. I want to use the same parameter in the group by column. This works nicely. However, I am not able to split and name the resulting columns dynamically. Can you please advice how to achieve this?

The red box shows what I want to make dynamic, and should be named according to the items in the array sql_table_unique_columns.

In this picture, what I want is the "group_columns" to be dynamically split in as many columns as specified in the array sql_table_unique_columns, with names from the array.

Aggregates are dynamic and works:

In advance, thank you for your time!
Kind regards

MartinJaffer-MSFT 26,091 Reputation points

2020-11-18T02:12:56.447+00:00
Hello @Mikkel Treu Os and welcome to Microsoft Q&A. Thank you for this query.

If I understand correctly, you want to make the Name as dynamic, and give it the column names concatenated together.

I did come up with an expression to turn the array of names into a single string. My thought was to store this in a parameter and refer to it in the Name as. When I tried this, the name of the parameter was printed instead!

toString(reduce($colnames, '', concatWS(',',#acc,#item), #result))

So far I have not found any way to make the Name as take dynamic contents.

Nasreen has some cool ideas. Please let us know if Nasreen's answer helps. Let me know if my understanding was correct.
Mikkel Treu Os 31 Reputation points

2020-11-18T08:21:39.627+00:00

Hi,
Thank you for your reply.
Your understanding is partly correct. Like you suggest, I want to make Name As dynamic, but not necessarily concatenated. I would like to get equally many columns as the array $sql_table_uniqe_columns contains. I.e. I want to dynamically keep the original column names after the group by operation.

I'll test the suggestions and let you know how it goes.

Accepted answer

Nasreen Akter 10,791 Reputation points

2020-11-17T18:55:08.843+00:00

Hi @Mikkel Treu Os ,

Try this. Thanks!
Please sign in to rate this answer.

2 people found this answer helpful.
MartinJaffer-MSFT 26,091 Reputation points

2020-11-17T23:18:31.037+00:00

@NasreenAkter-2040 Interesting suggestion, but could you please explain how it works? @Mikkel Treu Os asked how to change the Group by section, not the Aggregates section.

Nasreen Akter 10,791 Reputation points

2020-11-18T00:11:18.793+00:00

Hi @MartinJaffer-MSFT ,

I would be happy to explain why I suggested this.

What I understand from the problem description is that groupBy section is working as expected for one or multiple column name(s) provided in the parameter section as an array. Now if we want to display all the GroupByColumns (provided in the parameter section) with the correct value, we have to do something like the screenshot.

Now how it will work: if you preview the data, at the beginning you will see all the aggregatedColumns* (aggregated function here was last()) along with the group_columns column, and then all the groupByColumns** with the correct groupBy value (last() or first() both aggregate function will give you the correct result, as in the column (for that particular groupBy combination), all values are same).

*aggregatedColumns: columns which are not provided in the parameter
**groupByColumns: columns with the names which are provided in the parameter

Thanks!

Mikkel Treu Os 31 Reputation points

2020-11-18T08:46:38.633+00:00

Hi,
Thank you for taking the time and provided a good suggestion.
This seems indeed to do the trick! Elegant and easy to implement.

Should have thought about this approach, but was too focused on the group by tab and naming.

For completeness if anyone else finds this post, the auxiliary column "group_columns" which is no longer needed, can be removed with a select with Rule-based mapping. This allows us to remove one column, while keeping the rest which can dynamically change.

Show below:

Thank you very much!
Kind regards

Nasreen Akter 10,791 Reputation points

2020-11-18T15:07:14.453+00:00

Hi @Mikkel Treu Os , would you please Accept the Answer and Up-Vote. Thanks!

Anil Narayan Gawli 0 Reputation points

2024-06-27T08:44:14.5766667+00:00

Hi Nasreen, by using column pattern and providing last($$false()), it gives me only one row with aggregation of total rows
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Dynamic aggregation column name in Azure Data factory

0 additional answers

Your answer