I am trying to emulate a spreadsheet used by operations in which they enter values into a couple of fields and get a resulting value based partially on a VLOOKUP function. The VLOOKUP uses an approximate value reference in column A (filled with static values) to retrieve a value from column C (which contains calculated values). I have recreated the data in a memory tag of type dataset in Ignition, but the LOOKUP function doesn't allow for approximate value reference, only specific value, so far as I can tell. Wondering if anyone out there has any input on how to solve the issue.
In case its not clear: One of the values in column A is 7.7. If an operator enters a value of 8.0 into another cell on the spreadsheet the VLOOKUP function will pick the closest approximate value which, in this case, would be 7.7, and return the value in that row from column C. In Ignition I would have to perform the LOOKUP function with the precise value of 7.7 or I get an error.
Thanks in advance.
Q1: Is the lookup column in ascending order?
Q2: Vision or Perspective? (Add the tag to your question.)
Q3:Is "closest approximate value" always <= lookup value?
By coincidence, I had something similar come up this past week.
def vlookup(dataIn, lookupVal, lookupCol = 0, returnCol = 1, mode=1):
''' Lookup a value in a dataset with options for exact or closest match.
parameters:
dataIn: dataset to perform lookup function on.
lookupVal: value to look for in dataset
lookupCol: column in dataset to search for lookupVal (default is 0)
returnCol: column to return value from the result of the lookup (default is 1)
mode: lookup mode (default is 1)
0: exact match
1: nearest lower value
2: nearest higher value
3: nearest value higher or lower
returns:
value from dataset -or-
None under the following conditions:
- Mode 0: lookup value not in lookup list
- Mode 1: lookup value is lower than smallest value in lookup list
- Mode 2: lookup value is higher than largest value in lookup list
- Mode value is not between 0 and 3
'''
# Get list of possible lookupValues
lookupList = dataIn.getColumnAsList(lookupCol)
# Initialize closest row value
closestRow = None
if mode == 0 and lookupVal in lookupList:
closestRow = lookupList.index(lookupValue)
elif mode == 1 and lookupVal >= min(lookupList):
closestRow = lookupList.index(max([item for item in lookupList if item <= lookupVal]))
elif mode == 2 and lookupVal <= max(lookupList):
closestRow = lookupList.index(min([item for item in lookupList if item <= lookupVal]))
elif mode == 3:
closestRow = lookupList.index(lookupList[min(range(len(lookupList)), key = lambda i: abs(lookupList[i]-lookupVal))])
if closestRow is None:
return None
else:
return dataIn.getValueAt(closestRow, returnCol)
##########################################
sampleHeaders = ['Col1', 'Col2', 'Col3']
sampleData = [[1.0, 'A', 'W'],
[2.0, 'B', 'X'],
[3.0, 'C', 'V'],
[4.0, 'D', 'Z']
]
dataset = system.dataset.toDataSet(sampleHeaders, sampleData)
print vlookup(dataset, 0.7)
print vlookup(dataset, 2.7, 0, 2)
print vlookup(dataset, 2.7, returnCol = 2, mode = 3)
Output:
None
X
V
10 Likes
Excellent, this looks like it works just fine! Thanks a ton!
I did have to make a small adjustment to get it to work for me:
lookupList = dataset.getColumnAsList(lookupCol)
changed to
lookupList = dataIn.getColumnAsList(lookupCol)
Otherwise it kept telling me 'dataset' was not defined.
1 Like
Love to have you contributing to Ignition Extensions 
Kotlin's just like Python, only better in every way 
2 Likes
Paul, my friend, I'm barely proficient with what I know. 
That said, though, I'm looking into Kotlin with what spare time I have-- wait, what year is this again...?
2 Likes