0

I am working on Azure Data Flow and my requirement is to compare two date columns in derived column stage with an expressional and populate 0 or 1 based on that into another column. Conditions are: If (DateColumn1 >= 1st Jan of the previous year) and (DateColumn2 < 1st Feb of the previous year or DateColumn2 is null or empty) then Populate 'true' else 'false'

Firstly I need to get 1st Jan of the previous year and 1st Feb of the previous year. And post that in expression builder of derived column I need to populate true and false based on the condition. I tried serval ways to get this dates by using DatesFromParts: DATEFROMPARTS(YEAR(GETDATE()), -1, 1) but in data flow I do not find the function.

Can someone please help me out to write this expression.

Aswin
  • 4,090
  • 2
  • 4
  • 16
Nezko1
  • 15
  • 4

1 Answers1

0

You can use the below expression in derived column transformation of dataflow.

iif((DateColumn1>=toDate(concat(toString(year(currentUTC())-1),'-01-01')))&&(isNull(DateColumn2)||DateColumn2<toDate(concat(toString(year(currentUTC())-1),'-02-01'))),1,0)
  • The expression checks if DateColumn1 is greater than or equal to January 1st of the previous year (e.g., if the current year is 2023, this would be January 1st, 2022). The expression also checks if DateColumn2 is null or less than February 1st of the previous year. If both conditions are true, the expression returns 1. Otherwise, it returns 0.

The expression is tested for few sample data and below is the result. enter image description here

Aswin
  • 4,090
  • 2
  • 4
  • 16