0

Lookup in date range

Hi everyone,

I'm playing around a bit with the Ninox trial to figure out if it's something for use in our administration. So I'm trying out some of the functions and I'm stuck now trying to get a specific value based on the date. What I would like is that if a customer order falls within a certain date range a tariff level is assigned to it. So I created a table with the tariffs and their assigned start and end dates. I then added a field to the Sales table where I try to loopup the tariff based on the assigned order date.

My formula:

let OrderDate := 'Client order'.'Order date';
let SD := 'Accijnsniveau.csv'.'Valid from';
let ED := 'Accijnsniveau.csv'.'Valid to';
(select 'Accijnsniveau.csv' where SD < OrderDate and ED > OrderDate).Cost

The result is nothing. If I remove the "and ED > OrderDate part then I get all tariffs as a result. If I remove the "SD < OrderDate and" the result is also nothing.

What am I doing wrong? Is there some sort of formatting thing I should do before doing the numerical comparison?

4 replies

null
    • Alain_Fontaine
    • 3 yrs ago
    • Reported - view

    In the select statement, I would replace "SD" with "Valid from" and "ED" with "Valid to", thereby comparing the fixed value contained in the variable "OrderDate" with the values of those fields in each record, in turn, of the table "Accijnsniveau.csv". Of course, computing "SD" and "EV" is then not needed.

    • Test
    • John_Doe
    • 3 yrs ago
    • Reported - view

    Awesome Alain, that works. I don't understand why however. Could you explain this?

    • Alain_Fontaine
    • 3 yrs ago
    • Reported - view

    When a "select" statement is executed, each record of the designated table is presented, in turn, to the test. The test itself is thus evaluated in the context of each record, in turn, of that table. In this case:
    (select 'Accijnsniveau.csv' where 'Valid from' < OrderDate and 'Valid to' > OrderDate).Cost
    'Valid from' and 'Valid to' represent, in turn, the values of those fields in each record of your table, and they are compared to the - fixed while the records are scanned - value of the "OrderDate" variable, to decide which records will be included in the selection, which means in this case that the corresponding value of the field "Cost" is added to the sum.
    Hope this is clear enough...

    • Test
    • John_Doe
    • 3 yrs ago
    • Reported - view

    So if I understand correctly, by declaring SD and ED I made those into fixed values as well instead of comparing withing the table. I can understand that then the result doesn't match a case and is either all good or all wrong.

    Thanks for the explanation.

Login to reply

Content aside

  • 3 yrs agoLast active
  • 4Replies
  • 630Views