question

holomew avatar image
0 Votes"
holomew asked PRADEEPCHEEKATLA-MSFT commented

Azure Data Factory: Difference Between Two Record Values

Looking for a way in Azure Data Factory to get the difference of the same column from the latest record to next-latest record.

For example, how to create the "Difference" column here:

ID Value Difference
04 230 25
03 205 0
02 205 5
01 200

azure-data-factory
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

PRADEEPCHEEKATLA-MSFT avatar image
3 Votes"
PRADEEPCHEEKATLA-MSFT answered PRADEEPCHEEKATLA-MSFT commented

Hello @bartnoble,

Welcome to Microsoft Q&A Platform.

We can achieve this using Dataflows. Please do check the below steps in detail:

Step1: Add two source transformations with your source data (because we need to do self-join in upcoming steps)

104262-sourcetransformations.gif

Step2: Perform Self-join using Join transformation

104271-jointransformation.gif

Step3: Select transformation to rename few columns as temp Columns

104196-select1.gif

Step4: Filter transformation to take only rows which we are interested

104187-filter1.gif

Step5: Select transformation to remove TempID Column

104147-select2.gif

Step6: Derived Column transformation to generate Difference column

104263-derivedcol1.gif

Step7: Select transformation to remove TempValue Column

104148-select3.gif

Step8: Sink transformation to load data in to desired sink resource

104127-sink1.gif

Hope this helps. Do let us know if you any further queries.


Please "Accept the answer" if the information helped you. This will help us and others in the community as well.


select1.gif (550.1 KiB)
filter1.gif (318.7 KiB)
select2.gif (199.3 KiB)
derivedcol1.gif (813.2 KiB)
select3.gif (311.8 KiB)
sink1.gif (324.3 KiB)
· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Nice technique. That was awesome!

Thanks very much!

0 Votes 0 ·

Furthermore, is there a way to apply this per group?

For example:

Name Value Difference
ABC 230 25
ABC 205 0
DEF 505 85
DEF 420
GHI 150 50
GHI 100

0 Votes 0 ·

Hello @bartnoble,

Thank you for appreciating above solution.

Try to Split the groups in to different stream sets using conditional split transformation. And, on each stream set apply the same trick shared in above solution.

Check below link to know more details about conditional split transformation

https://docs.microsoft.com/en-us/azure/data-factory/data-flow-conditional-split

Hope this helps. Do let us know if you any further queries.

0 Votes 0 ·
Show more comments