CASE vs IF function to generated a calculated column

[Darren Gormley]

HI Community,

I'm trying, with out much success, to generate a calculated column when column criteria are met. I have my annotated data in a tall and skinny file, with a few columns of annotation and one column of data. What i'd like to do is generate a function that will allow me to average the treatment control data rows and subtract them from sampledata row... so, basically a background correction of my raw data to create abackground corrected data column. I'm just not sure the best way to go about it orhow to impliment it.I've been playing with both IF-ELSE and CASE-THen functions.. but just can't join the dots to get it working.

My data looks like this

 

And what i'm trying to do is somethign likeIF treatment = "media + CTG" then Average "media + CTG" (Em) this would give me the average background, which i could populate a new column with and then subtract that from the Em data to create a BG corrected column... or, if there are some smart people out there who may know a neat trick to do it in one step.

Any thoughts would be most welcome, i know this is simple to do in Excel and reference cells.. but i'd like to do it in spotfire as i QC my data here first, then if i'm happy, process it.. so it would save ducking in and out of Excel.

 

Cheers

Darren

[Shashank Gurnalkar]

What I understand is you want to compute the average of EM values where treatment is"media + CTG" and then subtract this value from the entire EM column. If so, you can use this expression.

[EM] - Case when [Treatment] = "media + CTG" Then Avg([EM]) Else 0 End

[Darren Gormley]

HI Shashank,

 

Thank you for taking the time to help with this solution. That solution nearly works... but it's only taking off the average "CTG + media"  value away from each Em value when the case statement is true, so Correcting the "CTG + Media values, the rest of the Em data is unaffected.

 

 

 

KR

 

Darren

 

 

[Dave Williams 2]

If you need an average of all the Em values for each treatment, you may need to add a calculated column as

 

Em - (Avg[Em] OVER [treatment])

 

I often find it easiest to create individual columns for parts of formulas to verify results and then combine as necessary

[Shashank Gurnalkar]

Okay, so another option is to create a calculated column with the expression:

 

Case when [Treatment] = "media + CTG" Then Avg([EM]) Else 0 End

 

And then use the following expression in a new calculated column:

 

[EM]- Max([Calculated column])

 

 

[Darren Gormley]

HI Dave,

 

Thanks also for commenting, but you slightly missunderstand the problem. I would like to calculate the average of one treatment group (Background Control "media + CTG") and use that value to take the "Backgroundl" of a different treatment group "treated", or all the samples actually. 

 

I have been able to caluculate the average "media + CTG" value, but that value only appears in the 4 rows where the annotation is "media + CTG", on for each replicate. I'd like the whole column to have that average value... then a simple calculated column would be Em-BG

 

I just can't get the whole column to populate with the average "media + CTG" value...

 

any ideas welcome.

 

KR

 

Darren

[Darren Gormley]

HI Shashank,

 

Thanks again for your suggestion and approach, this will work in this instance... but i often have different runs of the same experiments stacked on top of each other, and the "find max" option would remove the maximum average BG value from all the runs of the experiment rather than the experiment relevant background control.

 

If i could get the whole column for each experiment, to populate with that "media + CTG" average, i could do a simple subtraction... but it's total foxed me.

 

Thanks for your comments though, great to see this community willing to lend a hand.

 

KR

 

Darren

[Darren Gormley]

OK, Finally got it...  (with thanks from my new friends) and it might not be the best way.. but it's simple and, more to the point, works :) so first i calculated a column called Average BG Media + CTG that would only calculate average value for the Media + CTG, but added in the "over" plate term...  this will limit the calculation to each plate/experiments (so when i add data the calculation will still be valid) and returned a null value if not a Media + CTG row.Case

when [Treatment] = "Media + CTG"

Then Avg([Value (Em)])over [Plate Name]

Else null

EndThen, modifying the suggestion from Shashank, with another over function of the plate term, i calculated a second column which removes the correct "media + CTG" value from the correct "plate" term.[Value (Em)]-Avg([Average BG Media + CTG])over [Plate Name]Like i said, maybe not the sexiest solution... but it works :)Thank you for the support and help.