How to Populate 3D lookup table from Excel spreadsheet (.xlsx) using OML functions

manoj kandukuri
manoj kandukuri
Altair Employee
edited April 2024 in Altair HyperWorks

The “LookupTable3D” block approximates a non-linear function using interpolation on data provided as a "3D-table" with 3 inputs and an output.

 The three-dimensional table is then defined by specifying three vectors (x, y, z) of size m, n, and k, and a "3D-table", a vector of size m*n*k. Output of the block is the approximation of the block input using the bilinear interpolation method.

Table data, a vector of size m*n*k is a full factorial DOE vector corresponding with 3 input vectors.

For example:

The attached 'sample_spreadsheet.xlsx' file has 3 input vectors (column1, column2, column3) and output in column4.

image

 

Input 1 (i1) has 3 unique input values

Input 2 (i2) has 2 unique input values

Input 3 (i3) has 3 unique input values

ini1

5

10

15

ini2

100

120

 

ini3

20

30

40

Then the full factorial DOE vector should look like:

ini1

Ini2

Ini3

5

100

20

10

100

20

15

100

20

5

120

20

10

120

20

15

120

20

5

100

30

10

100

30

15

100

30

5

120

30

10

120

30

15

120

30

5

100

40

10

100

40

15

100

40

5

120

40

10

120

40

15

120

40

To generate Full factorial DOE vector using OML functions in model initialization block in activate..

clc, clear, close all

%find the path of the scm file

modelpath = fileparts(bdeGetModelFilePath(bdeGetCurrentModel()))

%read xlsx file stored in the same location as activate file

excelfile = 'sample_spreadsheet.xlsx';

xlsmat = xlsread([modelpath '/' excelfile]);

% read table without headerlines

xlsmat = xlsmat(2:end,:);

%find unique values in the 1st,2nd,3rd column

ini1 = unique(xlsmat(:,1)); % input_1

ini2 = unique(xlsmat(:,2));  % input_2

ini3 = unique(xlsmat(:,3));  % input_3

 

% create full factorial DOE based on number of unique values in the inputs

de = fullfact([length(ini1),length(ini2),length(ini3)]);

 

%initialize ouput: of vector of length = length(itp) * length(is) * length(it) with zeros

output_table = zeros(length(de),1);

 

%create table data based on Full factorial DOE

for i = 1:length(de)

       % finds row number of xlsmat which corresponds to input values

       idx = find(ismember(xlsmat(:,1:3),[ini1(de(i,1)),ini2(de(i,2)),ini3(de(i,3))],'rows'));

       %if empty fills with 0 if not takes the output from spreadsheet

       if isempty(idx) == 1

             output_table(i,1) = 0;

       else

             output_table(i,1) = xlsmat(idx,4);

       end

end

 

image

 

Welcome!

It looks like you're new here. Sign in or register to get started.

Welcome!

It looks like you're new here. Sign in or register to get started.