How to solve this MultiRow by using Sql Query Or using pyspark if possible.

Question

iif([Level]=0 and [MakeOrBuy]="Buy",0,

iif(!IsNull([Row-1:BuyParentLevel]) and [Row-1:BuyParentLevel]<[Level],

[Row-1:BuyParentLevel],

iif([Row-1:MakeOrBuy]="Buy" and [Row-1:Level]<[Level],

[Row-1:Level],Null())))

I have the above statements in My "MultiRow Tool",

I have the following input data as in picture ,am getting the right output. But am confused whether we can do such a thing if we want to do in a Sql ,if we have a table like tha input data. I need SQL equivalent/If possible PySpark Equivalent of this scenario.

Screenshot_20231115_040808_Chrome.jpg

FinnCharlton · Answer

@Surya1425  Only looked at your screenshot after I'd replied so my mistake, it's an interesting problem, might try and give it a go when I have some time

Surya1425 · Answer

BuyParentLevel volumn is in "CreateNewField" . So i think above query works if BuyParentLevel column already exists in the input. And Alteryx will dynamically check for previous row but Sql cant check previous row dynamically as per above code. You can try and compare in Alteryx vs your code. You can understand better. Thanks for the response anyway

FinnCharlton · Answer

Hi @Surya1425 , this is not particularly easy with SQL. The multi-row formula tool is capable of referencing itself as it is being calculated, no such function exists in SQL. One approach to this problem is to use recursive CTEs, some examples can be found here:

https://stackoverflow.com/questions/34338991/can-window-function-lag-reference-the-column-which-value-is-being-calculated

https://forums.oracle.com/ords/apexds/post/self-referential-lag-9805