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

Question

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

and this is the result returned

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?

Answer 1

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','')
    )
)

Result when pipeline name is "Daily":

Result when pipeline name is Monthly: