CODEWITHSUNDEEP
azureazure-data-factory
SHIBASHISH TRIPATHY
SHIBASHISH TRIPATHY

Reputation: 91

How to compare max min values of one column to another column in Azure Data Fatory

enter image description here

enter image description here

enter image description here

In the figure below as mentioned I have two data set and I have created a data flow in azure data factory. I want to get the max and min date from dataset1 and compare it with dataset2. As in the below example I have min(date_time) = 11-04-2020 01:17:40 and max(date_time) = 30-06-2020 22:00:00. So the dates which fall between this two max and min will be compared with Event_time column in dataset2 and all the matching dates should be printed as "Y" in Dataset1_Data_available column and non matching should be "N". Thanks!!

Upvotes: 0

Views: 1888

Answers (1)

Steve Johnson
Steve Johnson

Reputation: 8670

Please try this:

screenshot of my test data flow enter image description here

1.source1 is your Dataset1,setting of 'Aggregate1': enter image description here

enter image description here

Data preview of 'Aggregate1': enter image description here

2.source2 is your Dataset2,setting of 'Lookup1'(In lookup conditions,please make sure left column value never equal right column value): enter image description here

enter image description here

Data preview of 'Lookup1': enter image description here

3.use 'DerivedColumn1' to change the value of 'Dataset1_Data_available'

expression:iif(greaterOrEqual(Event_time, minDateTime) && lesserOrEqual(Event_time, maxDateTime),'Y','N') enter image description here

Data preview of 'DerivedColumn1' enter image description here

4.You can use 'select' or 'mapping' in sink to delete columns you don't need.Then output the result.

UPDATE

I create some test sample data. Data preview of sorce1(expected min Date should be '11-04-2020 01:17:40' and max Date should be '24-07-2020 08:09:02'): enter image description here

When we use min(date_time) and max(date_time) in 'Aggregate1'(min Date:'07-06-2020 04:30:40' max Date:'30-04-2020 00:56:56'): enter image description here

When use min(toTimestamp(substring(date_time, 4, 2)+'-'+substring(date_time, 1,2) + substring(date_time,6,14),'MM-dd-yyyy HH:mm:ss')) and max(toTimestamp(substring(date_time, 4, 2)+'-'+substring(date_time, 1,2) + substring(date_time,6,14),'MM-dd-yyyy HH:mm:ss'))(the same as we expected value): enter image description here

Upvotes: 2

Related Questions