How to create new column with dynamic existing columns with azure data factory/dataflow?

ri,shoshun 431

I want using azure datafactory or dataflow to convert a variable column file as below sample <data> .
Please tell me how can I do it? Thanks!!

<data>
-array parameter: ["flag1,typeA1,typeB1", "flag2,typeA2,typeB2",...,"flagN,typeAN,typeBN"]
-expression: flag = (typeA >= typeB)

-source csv: from salesforce, flag is old value, need to convert with new type value. flag1,typeA1,typeB2,flag2,typeA2,typeB2,...,flagN,typeAN,typeBN

true,1,2,false,4,3,...,true,2,2
true,2,1,false,6,5,...,false,3,3

-sink csv: Expected results flag1,flag2,...flagN

false,true,...,true <- flag1 = false(1 >=2), flag2 = true(4>=3), flagN=true(2>=2)
true,true,...,true

MartinJaffer-MSFT 26,211 Reputation points

2021-07-01T18:15:30.123+00:00
Hello @ri,shoshun and welcome to Microsoft Q&A.

If I understand correctly, what you are looking for is the map functions in DataFlow. The map function allows you to apply an expression to each element in an array.

Looking at your example array, the elements are string, and the components you wish to compare are separated inside the string by ,. We will use split function to break up the string.

something like...

map( dataArray, split(#item,',')[1] >= split(#item,',')[2] )
ri,shoshun 431 Reputation points

2021-07-02T03:48:11.887+00:00

Hi, @MartinJaffer-MSFT
Thank you for you reply!

I except image 2 [how to coding here?], Thank you for your help!!
MartinJaffer-MSFT 26,211 Reputation points

2021-07-12T17:07:13.87+00:00

@ri,shoshun

Is the data you want to split, coming as a column, or as a parameter?

Ther column pattern is used to test whether a column or column name passes a test, then applies logic to it.

If everything is contained inside a parameter, I am not sure how to apply the column pattern yet.
ri,shoshun 431 Reputation points

2021-07-13T00:43:39.317+00:00

HI, @MartinJaffer-MSFT
Thank you for you replay!

Use column pattern, can I get the current column name in the value expression?
I want to find the target value from array type parameter with the current column name and calculate it.
Example in Value expression:
parameter(useFlags) = ["flag1 | typeA1 | typeB1", "flag2 | typeA2 | typeB2", ...]
->byName (split (find ($ useFlags, split (#item,'|') == current column name), '|')[2])

Share via

How to create new column with dynamic existing columns with azure data factory/dataflow?

-source csv: from salesforce, flag is old value, need to convert with new type value. flag1,typeA1,typeB2,flag2,typeA2,typeB2,...,flagN,typeAN,typeBN

-sink csv: Expected results flag1,flag2,...flagN

Your answer