This blog will show how to use metadata stored in workbook but not in Column Label rows in column calculation. If you would like to see how to use metadata in column label rows in column calculation, please read the previous blog Using Metadata in Worksheet in Calculation – Part 1: Column Label Rows.
Origin’s Import Wizard allows user to extract variables from File name and headers of data file and store for future use. E.g. the following workbook. During import, variable XCORR and YCORR were extracted and we chose to store it in workbook’s page.info but not show it in column label rows. If you right click workbook window title and choose Show Organizer. A panel is added at the bottom of the window. Navigate to page.info -> USER.VARIABLES. You will see the two variables XCORR and YCORR. These are correction factors for X and Y data. We want to add them to raw data to get corrected values.
If you would like to follow along with this example, you can download the file DOC-2307_Store_Vars_in_OPJ.zip. Then open the OPJ file in Origin and go to Part2 folder In Project Explorer. You should see Book5.
1. Add two new columns, C and D in the worksheet. Set Column C as X.
2. Right click column C and choose Set Column Values… context menu.
3. In Set Values dialog, Choose Variables: Insert Info. Variables… menu
4. Insert Variables dialog opens. Select Book5 on the right panel, and select XCORR under USER.VARIABLES node. Make sure Insert as Link is checked.
5. Click Insert button. The variable is defined in Before Formula Script. It’s defined as string. You can see the syntax is page.info.user.variables.variableName$
6. If you want to insert it as number, in Insert Variables dialog, set Variable Type to be Numeric double before click Insert button. Or you can manually modify the variable in Before Formula Scripts to be
7. Set the Col(C)= Formula box as follows and click OK. Column C will be filled with every value in column A plus 5.30.
Repeat the process for column D to calculated the corrected Y data. You can go to Part3 -> Results subfolder in Project Explorer to see expected result. To see the formula, click the green lock icon and choose Change Parameters….
Some Notes about Insert Variables dialog.
- There are other nodes on Info. tab. Such as FileName and FileData, Subheader in Data file, etc. You can insert them as a variable as well. Usually they will be inserted as string variables. But as we mentioned in previous blog, you can use built-in Labtalk string functions and value() functions to extract useful information and convert it to number and use it in calculation.
- On right panel, you can select different workbooks, sheets, columns and see related information. Also depending on the node you select, you can also go to Labels tab to insert variables.
- If Insert as Link is NOT checked, static string or value will be inserted as variables so that if new file is imported, the variable in Set Values dialog will not update. So usually we recommend check Insert as Link checkbox.
Thanks for reading. I hope that this has been helpful.
If you would like to read more about some of the features and topics covered in this post, see the following pages in our online documentation: