export complete component table

Discuss with developer team, share useful tips, report bugs, request new features and anything else
Post Reply
Teun-de.Joode
Posts: 8
Joined: December 7th, 2018, 5:30 am

export complete component table

Post by Teun-de.Joode » December 22nd, 2018, 7:09 am

Hi, I've runned several samples and matched them against a template which contains a lot of target-components. Not every component is present every sample. In the blob table, target-components that were not detected are not reported. This results in the fact that all blob tables of the analysis are different. I would like to have a table with ret. times, volumes, etc., of ALL target components in every sample, including the ones which were not found, and then export in to excel. Is there a way to obtain this? I tried to import the template .csv file into a quantification, but that failed due to a 'I/O exception caught' error.

qtao
Posts: 131
Joined: October 7th, 2011, 10:16 am

Re: export complete component table

Post by qtao » December 26th, 2018, 10:41 am

There is no feature that shows the exactly same table you want. Investigator program provides some similar tables:

Investigator > File > Load all sample images with As-is option

Attributes tab shows aggregate tables for all samples by individual properties. It lists compounds that were detected in at least one sample. But it won't show compounds not detected in any sample.

The other workaround is to use Excel's Data > Consolidate function:
https://support.office.com/en-us/articl ... b2c9e36d9b
  1. Create a list of the names of all compounds with a header Compound Name
  2. Open a sample image > save a blob table > open it in Excel
  3. Copy the list of all compounds to the second sheet
  4. Data > Consolidate the two sheets by following the Excel's instructions
I tried to import the template .csv file into a quantification, but that failed due to a 'I/O exception caught' error.
I'm not sure about your question for this. A CSV calibration table needs to be in a specific format. The easiest way to create a calibration table is using Quantification > New Calibration... or New Calibration from Current Image menus.

Teun-de.Joode
Posts: 8
Joined: December 7th, 2018, 5:30 am

Re: export complete component table

Post by Teun-de.Joode » January 4th, 2019, 5:23 am

Alignment in Excel:
I found a formula on internet which can do the alignment trick:
Suppose the component names of the template are put in column A and the blob table (names, volumes, ret. times etc.) are in column B, C, D, etc.
Suppose you have 100 components in the template. The headers are in row 1. The alignment is based on the component names.
To align the blob table to the template, use the formula in a column to the right of the blob table:
=IF(ISNA(MATCH($A2;$B$2:$B$101;0));"";INDEX(B$2:B$101;MATCH($A2;$B$2:$B$101;0)))
Copy the formula to rows 3 -101. The matched names will be copied to the correct row corresponding to the template.
You can align the other columns (volumes, ret. times, etc.) by copying the formula to the next columns to the right, the INDEX column will change to C, D, etc. Template-components not present in the blob table are left blank in the aligned data.
Then copy the aligned data to a new sheet and paste it as 'values'.

Teun-de.Joode
Posts: 8
Joined: December 7th, 2018, 5:30 am

Re: export complete component table

Post by Teun-de.Joode » January 4th, 2019, 7:38 am

The alignment in excel is even easier using the VLOOKUP function.
Here's good explanation from Danny Rocks: https://www.youtube.com/watch?v=809m6kLTfgI

Post Reply