Multiplying one column by another

[Michael Hall 4]

Hi,

Probably a trivial question so apologies for that, but the manual and tutorial videos are pretty much worthless. I have a simple situation where I have a table of data in a mixture of energy units: KWh, MWh, btu, mmbtu, Metric Tons etc. What I want is a calculated column that has converted it all to MWh. Using a simple data set that only contains two different I came up with the following expression:

casewhen [AMT UNIT]="METRIC TON" then "12.7*[+1QTY]"when [AMT UNIT]="MEGAWATT-H" then "[+1QTY]"else "NA"end

The idea being, if the unit is metric tons it multiplies the quantity in +1QTY by 12.7. If it is megawatts it just returns the value of +1QTY.

What it's actually doing is giving me a string that says "12.7*+[1QTY]" or "[+1QTY]"

If I remove the "" then I get an error about "Invalid type for call function IF" even though there is no if.

 

So if someone can tell me what I'm doing wrong (and if anyone has a GOOD resource for learning that doesn't involve hours of googling in vain) I would be very grateful!

 

Thanks...

[Richard Pobi]

casewhen [AMT UNIT]= 'METRIC TON'then 12.7*[+1QTY]when [AMT UNIT]= 'MEGAWATT-H'then 1*[+1QTY]else 0end

The above should work, use zero after the ELSE or just eliminate the ELSE part. Using 'NA' will give you error becuase you asking the expression to return a number and a string data type. 'NA' is a string data type.

[Michael Hall 4]

Thank you, that works.