0

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?

Aswin
  • 4,090
  • 2
  • 4
  • 16
fabio.sitzia
  • 3
  • 3
  • So, you are using the same expression in two dataflows and expecting the above result? – Rakesh Govindula Jul 18 '23 at 14:14
  • 1
    Hi @RakeshGovindula! Thanks for your concern. No, I'm using this expression in a derived column of a data flow used by two pipelines, one executed every weekday, and one executed once a month on the first day of the month. No, I expect the result written below the image. The one in the image is what is returned to me by the expression as I wrote it in the image above – fabio.sitzia Jul 18 '23 at 14:27

1 Answers1

0

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

Rakesh Govindula
  • 5,257
  • 1
  • 2
  • 11