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.
- DateTime2 vs DateTime in SQL Server
- Generate Dates between date ranges
- Why (and how) to split column using master..spt_values?
- Oracle-DB, Joining 3 Tables Limiting the second without using LATERAL() or nesting
- How to convert sql table into a pyspark/python data structure and return back to sql in databricks notebook
- Get rendered SQL from CodeIgniter active record script
- Materialized view in Marketing Performance DB in Azure SQL database
- mysql delete under safe mode
- SQL SELECT WHERE field contains words
- SQL Server: group dates by ranges
- Get day of week in SQL Server 2005/2008
- The table has no ID, the date is contained in two columns
- Creating an index on a table variable
- How to drop all tables except 1
- Pyspark: Select all columns except particular columns
- How do I grant read access for a user to a database in SQL Server?
- MariaDB : create database and execute sql script without '<' character from the command line cmd.exe
- Find records with only one id
- MySQL - Get the first and last day of previous week
- Implementing iteration in Standard SQL (BigQuery)
- Concatenate dataframes in a loop where some of them may have no values
- SQL update with join is only updating one column
- Nested LAG function (kind of)
- Get SQL to get all records from a usertable and those matching uids from second table or return null
- SQL query to find duplicate rows based on multiple columns
- SQL Server Add row number each group
- How to calculate FIFO in SQL using matching overlapping blocks (using JOIN instead of UNION)
- How do I spool to a CSV formatted file using SQLPLUS?
- what is a foreign key and why you use it?
- What is semi-join in database?