CODEWITHSUNDEEP
spotfire
Brittany Enfield
Brittany Enfield

Reputation: 107

Calculated column based off values in another column in Spotfire

I just started using Spotfire and am having a bit of a tough time figuring out how to create a particular calculated column. I'm pretty sure I need to use the OVER function, but I haven't quite figured it out and there's almost no documentation or examples on the interwebs.

Here's a sample of the data I'm working with:

loc     wafer   x   y   pass    bin  
T25     10      2   37  1       13  
T25     10      2   37  2       6  
CMI     10      2   37  NA      13  
T25     10      2   37  NA      13  
T25     10      2   38  1       13  
T25     10      2   38  2       13  
CMI     10      2   38  NA      13  
T25     10      2   38  NA      13  
T25     10      2   70  1       1  
T25     10      2   70  2       4  
CMI     10      2   70  NA      1  
CMI     10      2   70  NA      1  
T25     10      2   70  NA      1  
T25     10      2   70  NA      1

The column I'm trying to create will be based off of the pass value. So, for each location, wafer, and x-y coordinate, whenever pass=1, I want to copy the bin value into the new column for that row. I also want that same pass=1 bin value to be copied into the matching location, wafer, x-y coordinate where pass is not NA. Here's what I would like to see:

loc     wafer   x   y   pass    bin newCol  
T25     10      2   37  1       13  13  
T25     10      2   37  2       6   13  
CMI     10      2   37  NA      13  NA  
T25     10      2   37  NA      13  NA  
T25     10      2   38  1       13  13  
T25     10      2   38  2       13  13  
CMI     10      2   38  NA      13  NA  
T25     10      2   38  NA      13  NA  
T25     10      2   70  1       1   1  
T25     10      2   70  2       4   1  
CMI     10      2   70  NA      1   NA  
CMI     10      2   70  NA      1   NA  
T25     10      2   70  NA      1   NA  
T25     10      2   70  NA      1   NA

My current expression is as follows:

CASE
  WHEN [pass] = "1" THEN Concatenate([bin]) OVER ([location],[wafer],[x],[y])
END

It copies the bin to any rows with pass=1, but not the the pass=2 rows.

Upvotes: 2

Views: 9895

Answers (1)

niko
niko

Reputation: 3974

EDIT to avoid having the [bin] value when [pass] = "NA", surround the whole thing in an If() like so:

If([pass]!="NA", First(If([pass]="1",String([bin]),"NA")) OVER (Intersect([loc],[wafer],[x],[y])), "NA")

I think you're on the right track, but this isn't really a simple expression! here's what I came up with:

First(If([pass]="1",String([bin]),"NA")) OVER (Intersect([loc],[wafer],[x],[y]))

so let's break this down:

  • First(...) is chosen because we want, well, the first result! the inline If() will return an NA for each row in which [pass] != 1, and you can observe this by changing First() to Concatenate()
  • If([pass] = "1", String([bin]), "NA") makes sure that we only ever record the value in [bin] or NA. without this, our result would contain all values for [bin] -- try removing the If() and see!
  • OVER Intersect([loc], [wafer], [x], [y]) creates a hierarchy of "nodes" (see documentation linked below) for each unique combination of these four columns.

NOTE that depending on the size of your data, this might be a very slow expression, due to the Intersect(...). if you find that it takes a long time to calculate when the data is refreshed, you may have better results with two columns:

  1. [ID] which is simply Concatenate([loc], [wafer], [x], [y])
  2. [Result] which is the above answer, except the end looks like ... OVER [ID] (we don't need to use Intersect() since we are only comparing across a single column)

for documentation on the OVER functions with examples, check the Spotfire help file at Advanced Custom Expressions. I know it's still not perfectly understandable; OVER functions are a bit of a stretch for the brain :)

just to add some context to your first attempt and why it wasn't working:

since you aren't doing any calculation on the value for [bin], you simplify by using the following case:

WHEN [pass] = "1" THEN [bin]

of course the expression that you have is not returning any value when [pass] != "1", as you haven't specified any other cases. to fix that, you'd need something like this:

CASE
  WHEN [pass] = "1" THEN [bin]
  WHEN [pass] != "NA" THEN ...
  ELSE null
END

Upvotes: 2

Related Questions