Search code examples
azureazure-data-factorymultiple-conditionsderived-column

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:

  • if it is executed in the daily execution pipeline and DataRef is a weekday then 'daily';
  • if it's Friday then 'daily | weekly';
  • if it's the last Friday of the month 'daily | weekly | monthly'.
  • If run in the monthly execution pipeline, all data will have 'monthly' frequency and value

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?

Solution

  • 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