Recreating a lookup table in Javascript

Question

So I have a spreadsheet for retrieving membership rates, the columns are Age, Duration & Rate. You simply look down the age column to find the age of the customer, then when you find that age you keep heading down to match it to the correct Duration, then in the final column will be the rate. A (very) small version of that might look like this;

Age,Duration,Rate
18,10,1.33
18,11,1.5
18,12,1.8
19,10,1.4
19,11,1.65
19,12,1.88
20,10,1.48
20,11,1.73
20,12,1.98

So someone age 19, duration 11 has a rate of 1.65. Someone age 20 with a duration of 12 has a rate of 1.98 - easy!

My question is two parts, I want to convert this into a web page where someone enters the age and duration to retrieve the rate. I'm pretty sure my best option for this is a two dimensional array like so;

var array = [[18,10,1.33],[18,11,1.5],[18,12,1.8] .. and so on];

are there any better options for this?

The second question is how do I best iterate over a two dimensional array (if that ends up being the best solution)? As I touched upon before I would need to be able to have an iteration that returns the rate based on a two criteria search. I believe this would consist of a two part iteration but iteration is such a weak spot for me that trying to grasp where in the loops to put my iterations is just brain melting. I think it would look something like so;

for (var i = 0; i < array.length; i++){
for (var j = 0; j < array[i].length; j++){
    //Do something... I think something like this
    If array[][j] == ageImLookingFor && array[][j+1] == durationImLookingFor
    then return array[][j+2] (which is the rate)
  }
}

Any help, advice or ideas I would be super grateful

Answer 1

A better option than using an array is to use an object (or Map) with properties (keys) that correspond to valid combinations of age and duration, effectively indexing your data by that key:

var list = {
     '18_10': { age: 18, duration: 10, rate: 1.33 }
     '18_11': { age: 18, duration: 11, rate: 1.5 },
     '18_12': { age: 18, duration: 11, rate: 1.8 },
     // .. and so on
};

This way you do not have to iterate over an array (cf. your question #2), but given an age and a duration (let's say in variables that have those names), you can write this to get the matching item:

var item = list[age + '_' + duration];

Of course, you should check that age and duration are valid integer numbers and that the item could be undefined when the combination is not known.

Here is a simple snippet (without any checks) you could use to base your web form on. It builds the above mentioned object from an array having the data.

// Data in array -- will be keyed later
var arr = [
    { age: 18, duration: 10, rate: 1.33 },
    { age: 18, duration: 11, rate: 1.5 },
    { age: 18, duration: 12, rate: 1.8 },
    { age: 19, duration: 10, rate: 1.4 },
    { age: 19, duration: 11, rate: 1.65 },
    { age: 19, duration: 12, rate: 1.33 },
    { age: 20, duration: 10, rate: 1.48 },
    { age: 20, duration: 11, rate: 1.73 },
    { age: 20, duration: 12, rate: 1.98 },
];

// Build map, keyed by age/duration. It will look like:
//   { 
//      '18_10': { age: 18, duration: 10, rate: 1.33 },
//      '18_11': { age: 18, duration: 11, rate: 1.33 },
//       ...etc
//   }
mapByAgeDuration = {};
for (var i=0; i < arr.length; i++) {
    mapByAgeDuration[arr[i].age + '_' + arr[i].duration] = arr[i];
}

// Fast retrieval function:
function getItemFor(age, duration) {
    return mapByAgeDuration[age + '_' + duration];
}

// I/O

var button = document.getElementById('findRate');
var inputAge = document.getElementById('age');
var inputDuration = document.getElementById('duration');
var outputRate = document.getElementById('rate');

button.onclick = function() {
    var age = inputAge.value;
    var duration = inputDuration.value;
    
    // Retrieve item for this age and duration
    var item = getItemFor(age, duration);

    // Output rate
    outputRate.textContent = item !== undefined ? item.rate 
                             : 'not a valid combination';
}

Age (18 - 20): <input id="age"><br>
Duration (10 - 12): <input id="duration"><br>
<button id="findRate">Find Rate</button><br>
Rate: <span id="rate"></span><br>

Answer 2

It depends. If you use hashes, you will have O(1) time on average, but O(n) on worst case.

If you prefer to optimize the worst case, you can use binary search to achieve O(lg n) both on average and worst cases.

function binarySearch(array, data, from=0, to=array.length) {
  if(from >= to) return -1; // not found
  var m = Math.floor((from+to)/2);
  for(var i=0; i<data.length; ++i) {
    if(data[i] < array[m][i]) return binarySearch(array, data, from, m);
    if(data[i] > array[m][i]) return binarySearch(array, data, m+1, to);
  }
  return m;
}
var idx = binarySearch(array, [18,12]);
if(idx > -1) array[idx];

Answer 3

Another approach is to leverage on the array.filter function. You have to reshape your data into an objects array:

var rates = [
	{'age':'18','duration':'10','rate':'1.33'},
	{'age':'18','duration':'11','rate':'1.5'},
	{'age':'19','duration':'12','rate':'1.8'}
];

function filterRate(item){
	if(item.age == this.age && item.duration == this.duration)
		return item;
}

function getRateByAgeDuration(age, duration){
	res = null;
	try{
		res = rates.filter(filterRate, {'age':age, 'duration':duration})[0].rate;
	}
	catch(ex){ console.log(ex);}
	return res;
}

document.write(getRateByAgeDuration('18', '10'));

Answer 4

Q1: You can use a hash table for your lookup.

var data = [[18, 10, 1.33], [18, 11, 1.5], [18, 12, 1.8], [19, 10, 1.4], [19, 11, 1.65], [19, 12, 1.88], [20, 10, 1.48], [20, 11, 1.73], [20, 12, 1.98]],
    object = {};

data.forEach(function (a) {
    object[a[0]] = object[a[0]] || {};
    object[a[0]][a[1]] = a[2];

});

// usage
document.write(object[19][11] + '<br>');
document.write(object[20][12] + '<br>');

document.write('<pre>' + JSON.stringify(object, 0, 4) + '</pre>');

Q2: A proposal with Array#some()

If you have sorted data, you could insert a short circuit, if the values are greater then needed.

var data = [[18, 10, 1.33], [18, 11, 1.5], [18, 12, 1.8], [19, 10, 1.4], [19, 11, 1.65], [19, 12, 1.88], [20, 10, 1.48], [20, 11, 1.73], [20, 12, 1.98]],
    object = {};

function getValue(p1, p2) {
    var result;
    data.forEach(function (a) {
        if (a[0] === p1 && a[1] === p2) {
            result = a[2];
            return true;
        }
        // short circuit for not found values
        return a[0] > p1;           
    });
    return result;
}

// usage
document.write(getValue(19, 11) + '<br>');
document.write(getValue(20, 12) + '<br>');