CODEWITHSUNDEEP
azureazure-data-factorymultiple-conditionsderived-column
fabio.sitzia
fabio.sitzia

Reputation: 3

ADF: Derived column with multiple (true) conditions returning string array

I have the following problem in a Data Factory data flow: I have two pipelines that extract data: one daily on weekdays, and one monthly on the first working day of each month. In the data flow I would like to insert a derived column, 'Frequency', that based on the date value in the 'DataRef' column, assigns a predefined array of string (separated by '|') values according to this logic:

For now I tried this expression (the third condition is not implemented), but obviously only the first condition is verified

enter image description here

and this is the result returned

enter image description here

But I would expect, e.g., where DateRef is 2023-07-14 (Friday), Frequency 'daily | weekly', or where DateRef is 2023-06-30 (last Friday of June 2023), Frequency 'daily | weekly | monthly'.

Any suggestions?

Upvotes: 0

Views: 839

Answers (1)

Rakesh Govindula
Rakesh Govindula

Reputation: 11464

Use the below expression to achieve the result.

toString(
    concat(
        iif(instr($pipelineName,'Monthly')!=0,'monthly',iif(dayOfWeek(DateRef) >=2 || dayOfWeek(DateRef) <=6, "daily",''))
        ,iif(instr($pipelineName,'Daily')!=0 && (dayOfWeek(DateRef) == 6),' | weekly',''),iif(instr($pipelineName,'Daily')!=0 && dayOfWeek(DateRef) ==6 && (minus(dayOfMonth(lastDayOfMonth(DateRef)),dayOfMonth(DateRef))<=6),' | monthly','')
    )
)

enter image description here

Result when pipeline name is "Daily":

enter image description here

Result when pipeline name is Monthly:

enter image description here

Upvotes: 0

Related Questions