Is there a provision to group a breaking sequence based on its continuity in Azure Mapping dataflow

user_4887345 0
Viewed 99

I wanted to group a breaking sequence based on its continuity using Azure ADF Mapping Dataflow.

Following is the blob file sample:

enter image description here

As shown above, there is a primary key column and a column containing breaking sequences, I wanted to create a third column(Expected output) which groups a sequence.

As 1,2,3 is the 1st sequence, it is having value 1, and 9,10,11,12 is the next sequence which has value 2 and so on.

azure-data-flow azure-data-factory-2 azure-data-factory
1 Answers

Use Windows transformation to compare current row with preview row. Then, use a 2nd Window transformation to increment the counter if there is a gap, i.e. current sequence - previous sequence > 1.

If you add a Source called source1 to a new data flow, you can paste this code using the Script button to show the script behind. Add a new-line with Enter at the end and copy/paste:

source1 derive(dummy = 1) ~> CreateDummyValue
CreateDummyValue window(over(dummy),
asc(year, true),
startRowOffset: -1L,
endRowOffset: 0L,
year2 = first(year)) ~> AddPreviousValue
AddPreviousValue window(over(dummy),
asc(year, true),
column1 = sum(iif(abs(year-year2)<=1,0,1))) ~> CompareAndBucket

I'm using my own column names from movies data, so replace the column names with your data columns. For you, movie should be replaced with Sequences and Expected Output is my "Column1".

user_73507880
Related