azure-data-factoryexpressionazure-synapse-pipeline

string_agg substitute used in Derive transformation in Azure data flow expression language


How do I convert this sql code "left(string_agg(zationClient, '; ') within group (order by zationClient) , 100)" as a Transformation in data factory specifically in azure data flow expression language?

I am expecting an output like this with delimiter "NAFDQ-Trulife; EAQVN - Trulife"

I understand that string_agg is not supported in data flow expression language.

I tried substring(concat(zationClient, '; '), 1, 128) but it doesn't give the required result.


Solution

  • enter image description here

    enter image description here

    enter image description here

    left(substring(toString(reduce(collect(con), '', #acc+';'+#item, #result)),2),100)
    

    enter image description here

    {
        "name": "dataflow1",
        "properties": {
            "type": "MappingDataFlow",
            "typeProperties": {
                "sources": [
                    {
                        "dataset": {
                            "referenceName": "DelimitedText1",
                            "type": "DatasetReference"
                        },
                        "name": "source1"
                    }
                ],
                "sinks": [
                    {
                        "name": "sink1"
                    }
                ],
                "transformations": [
                    {
                        "name": "derivedColumn1"
                    },
                    {
                        "name": "sort1"
                    },
                    {
                        "name": "aggregate1"
                    }
                ],
                "scriptLines": [
                    "source(output(",
                    "          id as short,",
                    "          con as string",
                    "     ),",
                    "     allowSchemaDrift: true,",
                    "     validateSchema: false,",
                    "     ignoreNoFilesFound: false) ~> source1",
                    "source1 derive(tp = 1) ~> derivedColumn1",
                    "derivedColumn1 sort(asc(con, true),",
                    "     caseInsensitive: true) ~> sort1",
                    "sort1 aggregate(groupBy(tp),",
                    "     con = left(substring(toString(reduce(collect(con), '', #acc+';'+#item, #result)),2),100)) ~> aggregate1",
                    "aggregate1 sink(validateSchema: false,",
                    "     skipDuplicateMapInputs: true,",
                    "     skipDuplicateMapOutputs: true,",
                    "     store: 'cache',",
                    "     format: 'inline',",
                    "     output: false,",
                    "     saveOrder: 1) ~> sink1"
                ]
            }
        }
    }