Is it possible to select values from different rows based on values from the current row?

Q&A
, ,
1

I have a dataset where I rank partitions of my data in two different ways using denseRank based on valueA and valueB, creating two columns rankA and rankB. The columns valueA and valueB are averages over the partition and so are equal for all rows in each partition. The result is something similar to the data below.

image
image594Ă—433 19.3 KB

Is there any way to create a calculated field that, for a line with rankA = N, returns the value of valueB in the partition where rankB = N?

2

try creating a new calculated field maxOver({value_B}, [{rank_B}], PRE_AGG) and then create the field that returns value_B for the rows rankA max(ifelse({rank_B} = {rank_A}, {value_B}, null)) over (partition by {rank_A})

1 Like
3

Hi @gcasas,

Hope everything is well with you! Just checking back in since this thread hasn’t received a response in a while. (Was lmentis’s reply helpful to you and/or) were you able to find a solution yourself in the meantime? Please help the community by marking this answer as “Solution” or following up in general within the next 3 business days!

Thanks!

4

Hi @gcasas,
Since we have not heard back further, I’ll go ahead and mark the solution. However, if you have any additional questions, feel free to create a new post in the community and link this thread for relevant information if needed.