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

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

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 