Idea: Validate Data - scale/replace an incoming field?

cmollo · May 6, 2013 1:06PM

This is probably a simple question with an obvious answer, but I cannot find the answer in the Forums or KnowledgeBase. I want to convert a field from text to numeric and then scale that field. I am trying to accomplish this with the Field Organizer block. I would like to use a formula like this:
VALUE(this same field) / 100.0
Of course "this same field " is a circular reference, not a valid formula field reference. So, the best I can figure out is to use two Field Organizer blocks. In the first block I create a new field, let's call it [field2], with a formula:
VALUE[field1] / 100.0
Then in the second block, I delete field1. Is that the best solution, or is there a better solution?

Thanks! Chris

paola · May 15, 2013 5:20AM

Hi Chris, Could you please post a small sample file (iok or Excel), so we can see the format of the values in the input field? Thanks

cmollo · May 15, 2013 8:38AM

Hi paola,

Thanks for the response. Yes, files are attached to this post. The input data has a field named "Gas Price," which is in cents. I want to convert that field to gas price in dollars. I can do that using two Field Organizer blocks (as shown in the example iok file). I would like to accomplish the conversion using a single Field Organizer block (or a single block of some kind). I just can't believe there is not a simpler way to scale a field. I want to scale the field "in place"... I just want to specify that a field be multiplied by some value and the result replace the original value.

Chris

tjbate · May 22, 2013 12:52PM

Chris - No, you have not missed a trick...at the moment the two separate Field Organiser blocks are required because you cannot delete the existing [cents] field in the same block that you are using to convert [cents] to formula field [dollars]. That is why the second block is currently required... to delete the [cents] field. You could choose to eliminate the second Field Organiser block overhead by leaving the [cents] field in the data set, and simply hiding it globally in the DataExplorer interface using:

Data > Manage fields {field} Hide

However, the extra unused field/column would add to the RAM footprint of the in-memory Report IOK file data set, so this is not ideal.

I have re-classified this as an Idea to use another block, possibly the Validate Data block, to perform this kind of operation.

cmollo · May 23, 2013 8:27AM

OK. Thanks for the insight. It is good to know.

	scale_field_example.iok	18K
	Large_2012.csv	18K

Welcome!

Categories

Ideas Parade

Tagged