lookupOne not working like I thought

I need to import data into a table from an external table. I imported the external data (Ninja) into Grist as a table.

Then I want to get values from the Ninja table where the date and time match. Both time fields are 2 decimal place numbers.

I thought this would work, but it didn’t.

Ninja.lookupOne(Date = $Date, Time = $Time).LiqScoreHTF

Help please

In your screenshots, there are no overlapping combinations, so no obvious value that’s wrong. When a lookup fails to find anything, it returns an “empty” record, whose columns have default values – those are 0 for a numeric column, that’s why you are seeing 0 in the result.

As for what could go wrong, I’d check the types of columns. In particular, check that the column Date has the same type in both tables (e.g. NOT type Date in one and type Text in the other), and same for the column Time.

Another danger is using floating-point numbers for lookups. Lookups rely on exact comparisons, and floating-point numbers are notoriously tricky with exact comparisons. Whole numbers should be fine, but numbers like 11.30 don’t have a precise floating-point representation, and are always represented approximately, so it’s possible to have two values that look very similar but are not equal. You can detect many discrepancies by setting a high enough “Max Decimals” value, but to be generally reliable, I’d use a Text column, or an Integer column storing 1130 instead of 11.30.

Thanks Dmitry

When I didn’t get the expected result the first time, I checked the dates and they matched. I just added an IntTime field so I could compare integers. And still no luck. Do I have the formula right to lookup the LiqScoreHTF field?

Ninja.lookupOne(Date = $Date, IntTime = $IntTime).LiqScoreHTF

seems like it should be easy, but I got something wrong

I tried a simplified test. Just one day for each date in my lookup table (Ninja). then I just looked for the date. It still returned zero instead of the result field

I’m doing something wrong but can’t figure out what it might be

It worked when I use IntTime for the reference. There is something wrong with my Date comparison

my bad. When I imported the data I switched month and year



sorry about that :wink:

Whew! Glad to hear it wasn’t a bug with lookups! :sweat_smile: