Changing values: Add formulae fields or use Search/Replace?

elitem · September 19, 2011 12:15PM

Hi guys - I would like to find out how to do a simple update to cells, similar action to an MS Access "update" query. Where I select and field, select criteria (for the same of a different field), and what to update the cells into if the criteria is met.

For example, I have a table of people's details i.e. names, address etc. Some of them have an email address in the email field, and for some the email field is null. I want to create a new field which will be called "Has Email?" and will be updated with either "yes" or "no" depending on whether or not the email field for that record has something in it or is null.

Can you help? Thanks Elite

tjbate · September 19, 2011 2:48PM

Elite - Omniscope uses formulae and/or Search/Replace and Record Filter blocks to detect alert conditions or data quality issues, setting 'flags' in fields that show on filtered alert/data quality tabs as calls to action.

In this case, you could use an IF statement defining a Formula field called something like [E-Mail Status]

IF([E-mail]=null, "No", "Yes")

You do not always need to create a separate Formula field. You could, for example, set a Search/Replace operations block to replace all null (empty) values in the [E-Mail] field with something like "no_value@e-mail.com"

Mees · September 22, 2011 6:21AM

Thom, an update query type of functionality might be useful in case of replacing values other than null. Currently it is quite easy to do to be honest, just by creating a new formula field and use e.g. an IF statement. However, that creates a new field and if you want to remain the original field name it requires an additional field organiser block. It would be useful to have an additional option (eg next to the "Raplace all values" option in Field organiser block) to replace values based on a where clause.

elitem · September 26, 2011 6:07AM

Hi Tom, thanks for the comment - I've just tried to do the formula as you suggested above, but getting an error. I'm attaching a screenshot. What am I doing wrong?

Thanks
Elite

September 26, 2011 6:25AM

Hi Elite,
you forgot to put parentheses ( ) around the IF formula.

Regards,
Antonio

elitem · September 26, 2011 6:33AM

Hi Antonio. Thanks for the tip. I'm still not managing it. I'm using the following formula:

(if)[email]=null, "no"

This is obviously incorrect. I'm trying to say: if the email field value is null, then update the cell in this field to "no". Can you type the correct way for me?

Would appreciate your help. is there somewhere I can see examples to different types of queries? any material explaining how to use the different functions and the exact language of writing the queries?

Thanks
Elite

September 26, 2011 6:39AM

As Tom suggested, the correct formula is IF([Email]=null, "no")

Refer to http://www.visokio.com/kb/functions-guide for more info about functions and formulae.

Regards,
Antonio

tjbate · September 26, 2011 6:41AM

Elite - the IF( function should be in green...with no leading parenthesis...try selecting the IF function from the green drop-down function list below. Green is for functions, and blue is for fields/columns

elitem · October 10, 2011 7:59AM

Hello again, I'm trying to use the attached formula and getting an error, why?

October 10, 2011 9:15AM

Hello Elite,

You miss the open parenthesis '(' . The correct formula is IF([Organisation]=null, "n/a")

Regards

steve · October 10, 2011 10:14AM

You're also using square brackets wrongly.

If you insert field references using the "Insert field" option, you don't need these (and they are incorrect).

You only need square brackets when you are typing the field name manually.

So, either manually type IF([Organisation]=null, "n/a")

Or type IF( then use "Insert field" and choose Organisation, then type =null, "n/a")

NB. If you are typing your field names, and the name doesn't contain special characters or spaces, you can omit the square brackets too.

elitem · October 10, 2011 11:39AM

Thanks Steve, yes that works!

elitem · November 7, 2011 12:26PM

Hello, I have another query I want to run through you. I need to know what Omniscope uses for the Access "or". The idea is:

(IF([Mastercard issuers] = "True", "Yes") or IF ([Mastercard issuers]= "false", "No"))

I'm getting an error because of the "or". I'm not sure what term to use...

Thanks,
Elite

steve · November 7, 2011 12:51PM

It's the Excel syntax we follow most closely:
IF(OR(test1, test2, test3), ...)
Alternatively IF can have multiple clauses:
IF(test1, result1, test2, result2, result3)
But in this case, don't you simply want:
IF([Mastercard issuers] = "True", "Yes", "No")
?

elitem · November 8, 2011 4:40AM

Hi Steve

In this case I want (in simple English): if field value equals "true" then update value to "yes", and if field value equals "false" then update value to "no". I simply want to convert a True/False field to a Yes/No.

steve · November 8, 2011 7:50AM

If your field contains solely true/false, you can use this:
IF(field="true", "Yes", "No")

If it also contains other values, use this:
IF(field="true", "Yes", field="false", "No", field)

This will create a new field; you then need to convert to static values and delete the original field to complete the replacement.

But you might find it easier simply using Data > Operations > Search/replace.

elitem · November 9, 2011 7:04AM

Alright, Search/Replace is the solution for me! thanks Steve much appreciated

Welcome!

Categories

Ideas Parade

Tagged