Free Trial

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

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

Surya1425
5 - Atom

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.

 

3 REPLIES 3
FinnCharlton
13 - Pulsar

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-valu... 

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

Surya1425
5 - Atom

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
13 - Pulsar

@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

Labels
Top Solution Authors