Search & replace doesn't accept formulas. You need to use Field Organiser and add a field, then tick Formula.
If you simply want to copy another field, you can instead just duplicate the field using Field Organiser.

I cannot duplicate the entire column since some fields contain data, only some fields with no data I want to use the formula. Are there any ways to solve this?

Use a formula like this:
IF([Original field] = null, [some other field], [Original field])
This will take the original field value if it is populated, otherwise the other field value.

Steve
When I enter formula, there is no error. But when I click OK, it shows "Error - This field has circular references in its formula, Until you correct this, all formula evaluation will be disabled and you may see incorrect results"

In Omniscope, unlike Excel, formulas contain field references. Field A's formula cannot refer to Field A. Also, Field A cannot refer to Field B if Field B refers to Field A.

Perhaps you need to add a new field in Field Organiser containing your formula, which refers to two other fields.

Thank you for your advice, it should work this way. I have to create duplicate of the column to put formula. Can I do it one time for all columns? for example, I have field A to Field Z and want to copy all 26 fields and put next to Field Z.

You can't automate this. I'm a little unsure why you'd want to. Can you describe your data in more detail and what you are trying to achieve? Attach a file here if possible

Hi,

Please see attached details, I give you sample data and explain what I would like to do. Please advise the method and let me know if you need more information.

Also, how can I change the date that is a 'text' format to be 'date/time' format?

Thank you very much :)

This looked quite interesting so I gave it a go, I figured the best way to calculate your best price is to reorganize the data using a de-pivot so that it compacts it all into as few columns as possible to reduce the need of creating many columns of formulas. Though will have to read down and sort it accordingly for each Supplier.

I also had to convert the From X Units into a numerical field for the actual Best Price calculation to search for "lower From X Units".

Nitiwan - To change data typing from incoming Text to Date/Time, in the DataManager workspace you can use the Field Organiser block....when you change a Text field to Date/Time, you specify the format to read the incoming text, and the format to store/display the converted Date/Time in Omniscope.

You can also add a formula field using the TEXTTODATE function to convert the text field as documented here:

http://www.visokio.com/kb/functions-guide

Thank you very much daniel and tjbate.

I now can do de-pivot and change text to date. However, the formula to search for lower value is quite complicated and need time to concentrate.
Daniel, I have attached another sample file with additional data of location, please can you confirm if this is still work with the formula you advise earlier. Many thanks again.

-Edit- use the next posts attachment not this one

Yeap seems to work fine, Ive redone it in data manager to show the de-pivot operation I used and the input of the formula as a new field.


The formula isn't too bad, just take each line and brackets step by step. It will help if you look up the functions page to understand what each function does.

Apologies had to revise the formula slightly to include a subset function for the location field.

Hi Daniel, thank you very much. It seems work for me at the moment, I may get back to you with more questions once start using the data. Many thanks again :)

Hi again. Please can you advise how can I sort the column in horizontal (from left to right), Thank you very much.

You can use the Pivot Operation to condense it back into multiple columns. Just follow the operations as above. You wont need the Price field anymore as the Best Price is the new set of values. Also just to finish off you will need to rename the column headings which you can do with the field organiser if you load it up into Omniscope.

I delete the 'price' but it affect field 'best price', some data under 'best price' disappear. Also, if I want to put the field sequence as 401, 301, 201, 101, 1.. how can I do that?

You have to change the best price column to static values then. If you do it in the data manager operations it wil automatically convert it. It will also make it easier to update your file.

Below you can see that if you sort the columns before you pivot, you will get the order you want.

Hi Daniel
Thanks for all comments, all is going well so far. I still have more tasks to do on omniscope and now need your advice on function 'Expand values'. Please see attached original data and output that I require. My problem is that data in last two rows are not in the correct column on omniscope ('date field' contain price data, 'price field' contain unit data). Please advise what should I use as separator.
Thank you.

I personally wouldnt use the "Expand Values" as it follows a set rule so it won't split it accordingly. I've used a combination of if(), left(), right() & len() functions to divide the single cell out.

Left() takes the specified number of characters in a text string from the left.

Right() same as above but from the right side

Len() adds up the total number of characters in a text spring and returns a count of the characters.

If() - where used in conjunction with Len() you can start to specify parameters to compensate for the missing sections of the data.

Thank you! I will try using this formula.