As Many of you experienced while converting a Dataflow to Data prep(data recipe) you could see some error in converted Recipe Compute relative window functions . because not all elements in data flow having corresponding capability in recipes. so in this blog we are going to see how rebuild them using other functions in recipe.
i) Self-referential ComputeRelative in Data flow( a computeRelative node with saqlExpression that references the column it generates) is not supported in Data prep.
eg:
a) To calculate total count of records in computeRelative based on some data grouping.
Column Name : Count
Dataflow expression : previous(Count)+1
b) To calculate Cumulative Amounts or Age … in computeRelative based on some data grouping.
Column Name : TotalAge
Dataflow expression : previous(‘TotalAge’) + Age
Solution :-
a) To calculate total count of records in computeRelative based on data grouping.
For computing total records based on data grouping can be done using Awesome Summer ’22 release straight function Row_Number in recipes
Second Approach :-
we can also able to compute total records in particular data grouping in an another way using multirow Aggregate function Sum(field name). but which is not straight approach we have to create extra a column to get it done.
Step 1 : we have to create Count of records column using power of one approach. Refer below screenshot
step 2 : we have to aggregate the records count column using SUM(field Name) function. then we have to drop the previously created records count column in final dataset. Refer below screenshot
b) To calculate Cumulative Amounts or Age … in computeRelative based on some data grouping.
For computing Cumulative value based on data grouping can be done using straight function SUM(Field Name) in recipe.
