Calculated column based on another row (lookup)

[Przemek Stasica]

Hi, I can't get my head around a thing that would take a single expression in excel..

How to base thevalue of a calculated column on a value in another row and column

(all categorical)

I suspect this isabout using node navigation, intersects ans some form of hacked aggregation but, forgive me, I'm not capable of understanding the manual, whichdoes not seem to be giving the full syntax and definitions...

Ask: to pupulate ColX take the value of ColC from samerow, find therow where ColA equals this value and return the value of colB for that other row.

colA | colB| colC | ColX

AA | BQ| AT | ...

AV | BE| AY| ...

...

AT | BS | AO | ...

...

AY | BP | AM | ...

 

Result:

colA | colB| colC | ColX

AA | BQ| AT | BS

AV | BE| AY| BP

...

AT | BS | AO |

...

AY | BP | AM |

 

Cheers!

[Gaia Paolini]

I think the easiest thing would be to join the table to itself by defining the match between colC of the current table equal to colA of the joined table, then retain colB (which would be automatically renamed to colB (2))

[Fabian Duerr]

Yes, this would work. It might cause a cyclic dependence which can cause other problems. So, if you want to use your analysis file again (update) you can avoid this by doing the following steps:

 

Add a new data table with your current table as source. It's basically just a copy.

Then do the join that Gaia suggests by adding the column from the original table to the new table.

Continue further work with the new table

[Przemek Stasica]

Many thanks for your tip. However, I'd be wary of making self-joins or duplicating as my real table is of substantial size. Also the real use case has somewhat more complex lookup logic - I just wanted to get my head around principles how you could look things up. 

I ended up preprocessing the data row-by-row before ingestion to Spotfire.