How to get 1st date of previous year in Azure Data Flow and how to use nested IF in data flow
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.
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
DateColumn1is 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 ifDateColumn2is 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.
- Can anyone please explain this solution of hacker rank Binary Tree Nodes?
- Nesting queries in SQL
- Extract previous row calculated value for use in current row calculations - sql
- How can I select rows from a table where the sum of rows in a joined table is greater than a number?
- Generating time series between two dates in PostgreSQL
- Dates with no time or timezone component in Java/MySQL
- Group close happening events together in ClickHouse
- Is it possible to create a temp table on a linked server?
- Get most recent using datetime
- How can I delete one of two perfectly identical rows?
- How to use SELECT DISTINCT and CONCAT in the same SQL statement
- Mysql query search a string in all columns of a table
- Are PostgreSQL column names case-sensitive?
- Django: how to effectively use select_related() with Paginator?
- PHP: exec() returns status '0' (successful) when executing failing PostgreSQL scripts
- SQL Azure import slow, hangs, but local import takes 3 minutes
- Is TRUNCATE a DML statement?
- MySQL Cannot Add Foreign Key Constraint
- Max and Rank not returning expected results
- Boolean vs tinyint(1) for boolean values in MySQL
- What is the syntax for an inner join in LINQ to SQL?
- "item has already Been added" added in audit
- Merging 2 tables from different databases using MERGE INTO
- How to query a dynamic list of strings using SQL?
- Gaps between primary key id in sql table
- DB Design: how to indicate if a column data is public or private
- SQL Server + convert to text to HEX UTF16 Little Endian
- TO_CHAR to format decimal places not working for whole numbers
- Extracting value from comma-delimited string in Teradata
- How can I shuffle rows in a SQL Server table after insert? Newid doesn't work as expected for some reasons