convert excel vba to diadem script

[hamiltoncj]

I have a working excel spreadsheet that can interpolate z given and x,y using a matrix lookup table.  I am attaching the excel file.  I would like to import this functionality into Diadem.  Basically, I collect Engine Speed and System Pressure, and I would like to know what the resulting Compressor Output (l/min) equals.

This data is being collected from a vehicle at 100Hz sample rate, and this would greatly help with the data analysis.

The excel user defined functions are from www.tushar-mehta.com

Any assistance would be greatly appreciated.

[Hassan_Atassi]

Hi hamiltoncj,

Are you trying to pull the equations from the excel sheet or the actual data?

[hamiltoncj]

Hassan,

Thanks for following up with me.  To answer your question, no.  I do not wish to pull the data or the equations if possible.  I probably was not clear enough in my explanation before.  My DAQ is on a vehicle collecting ~130 channels of data at 100Hz for periods of 1-4 hours.  This is a fairly decent amount of data that exceeds the capabilities of Excel.  At the completion of the measurement I would like to generate a new channel based off of two recorded channels.  I am unsure how to code Diadem to automatically create a new channel based off of a lookup table.  I was able to find a response from Brad Turpin to another question regarding a 2-D lookup table, and I think that this gets me closer as I'm am able to create the Lookup table using his script.  My question is now how do I reference the lookup table to calculate the new channel (i.e. Compressor_Output)

"Re: How do I code a 2-D look-up table in a DIadem script?

 

11-03-2006 11:06 AM

Hi aviatrix,

I went ahead and built an example for you of how DIAdem can tackle a lookup table.  Note than in the example it loads the raw lookup table values into the first Group, creates a high-definition lookup table in the second Group, then executes 3 individual requests to lookup interpolated values for arbitrary [X,Y] pairs.  The function "GetInterpValue()" that I created returns a VBScript Null if the X or Y value is out of range.

In a real application, you would want to create the high-definition lookup table Group one time, then leave it for as long as you will need to reference it.  You do NOT need to recreate the high-definition lookup table Group multiple times for multiple lookup requests.

Brad Turpin
DIAdem Product Support Engineer
National Instruments"

I am attaching another excel file and a portion of one of my measurement files to help explain. 

[Brad_Turpin]

Hi hamiltoncj,

I looked at your 2 Excel files you posted and understand that in Excel you can ask for the interpolated Z value from arbitrary X and Y input values, using the XYZ lookup table (matrix).  What is your ultimate goal, though?  I'm betting you don't really want to request one Z value but rather lots of them.  Typical DIAdem customers calculating engine maps want to generate an interpolated and colored 3D surface for the measured data values.  The Characteristic Diagram 3D graph type in REPORT will do this automatically with no programming.  DIAdem also has extensive 3D interpolation and lookup functions so that you can create the desired 3D surface very quickly using a variety of algorythms.

Here's a picture of what your original Excel data looks like in REPORT,

Brad

[hamiltoncj]

Brad,

Thank you for your time and assistance.  You are correct that I don't want to just do one at a time.  My ultimate goal is to create a new channel with the returned z values and then I will be summing up the values to give a total compressor output in liters for the entire run.

The compressor is only running when "Governor" = 0

The "Compressor_Speed" = "Drv_Ratio" x "Eng_Speed" when the above condition is true.

Using the new values: "Compressor_Speed" as X and "System_Pressure" as Y create a new channel "Compressor_Output" using a lookup table to interpolate the values.

So...  Given:

	Recorded Values			Generated Values	Calculated Values	Lookup Table Values
Row	Governor	Eng_Speed	System_Pressure	Drv_Ratio	Compressor Speed	Compressor_Output
22880	0	1255	9.213	1.08	1355.4	Interpolated value
22881	0	1255	9.213	1.08	1355.4	Interpolated value
22882	0	1255	9.213	1.08	1355.4	Interpolated value
22883	0	1255	9.213	1.08	1355.4	Interpolated value
22884	0	1254	9.213	1.08	1354.32	Interpolated value
22885	0	1254	9.213	1.08	1354.32	Interpolated value
22886	1	1254	9.216	1.08	0	0
22887	1	1254	9.216	1.08	0	0
22888	1	1254	9.216	1.08	0	0
22889	1	1254	9.216	1.08	0	0

 Then after running this on the entire data set I will be summing "Compressor_Output" to figure out the total ammount of air processed by the compressor.

[Brad_Turpin · Accepted solution]

Hi hamiltoncj,

It took me a while, but I believe I have a nice solution for you.  I reworked the script you already had to process the entire set of measured XY values and interpolate the corresponding Z value for each measurement.  I then created a REPORT layout that shows the interpolation surface, the original lookup values (square markers) and the measured XY points (grey circles).  I also created a 2D and 3D surface showing just the measured interpolated surface and its XYZ points (grey circles).  Finally I summed all the values of the interpolated Z channels and displayed this result on each REPORT sheet.  To make the calculations and graphing run faster, I had the script remove all the Governor=1 rows from the measured data as well as any measured XY value rows which were outside the defined lookup table XY range.

I quoted the "Summed Compressor Output" in liters as you suggested, but I'm not convinced that the units I found in your Excel spread sheet (X=1/min, Y=bar, Z=l/min) support that conclusion.  It seems to me if I sum a bunch of l/min values I'll get a value that still has its unit=l/min.  Don't we need to multiply the sum by the number of minutes between measured data points (sampling rate in units of minutes)?  If so, then just make that change in line 119 of the script-- this is where the "Sum_Output" property is calculated and added to the Z channel.

Ask as you have questions,

Brad Turpin

DIAdem Product Support Engineer

National Instruments

[hamiltoncj]

Brad,

Thanks for all of your assistance.  This is exactly what I was hoping for.  Your scripting abilities are second to none.  Special thanks to NI for supporting this forum and others like this.

[mehmetsy]

Hi All

i just come accross with your conversation while i was looking for some information regarding the Compressor Map interpolation. i am not sure how familiar you are but attached you can see a typical compressor map where you have 4 different parameters (Pressure Ratio, Mass/Volume Flow, Corrected Speed lines and Compressor Efficiency)   plotted together.  i am looking for a interpolation script where i can calcualte the compressor efficiency based on those other 3 parameter. i was thinking to create 2 separete 2D interpolation parallel. Do you guys have any idea about such a case ?

Regards

M.S.YILDIRIM