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

manoj kandukuri

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.

 

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