I have a question regarding how to iteratively loop and merge filtered data records.
I am working with a dual tab excel file, and after reading in data and merging/filtering, my one lined excel table looks like below:
Compound(Prop1) Prop2 Prop3 Prop4 ... Prop20
AAA-000 Data1 Data2 Data3 Data19
I want to create a larger table containing many more compounds, however this does not work when I try and read many files into the initial excel reader (to get the data into the form above, i use a transform columns to rows component and then pilot script (ithproperty function).
I think the process needs to occur iteratively in order for it to work, i.e:
1. Read in each of 2 tabs of one excel file separately
2. Filter out undesired properties
3. Transform rows to columns
4. "Ithproperty" function (create the Property "Compound" for each data record)
5. Merge all properties to get what is shown above.
6. REPEAT AFTER FIRST CYCLE HAS BEEN PROCESSED
7. Merge each record based on "Compound"
Any help would be super helpful!!
Cheers;
Oliver.