Forum Discussion

charlito's avatar
charlito
New Member
3 years ago

LOOKUP VALUE BETWEEN DATES AND MULTIPLE CONDITIONS

Hello!!   This may be already posted, but can't find it or related.   I have 2 tables, a master table like this:   KEY BND SEC START_HOUR END_HOUR REPS 8 S2 18/04/23 5:00 pm 19/04...
different times
LOOKUPVALUE
multiple conditions
FreemanZ's avatar
FreemanZ
Icon for Super User rankSuper User
3 years ago

hi charlito 

 

it would be clearer if you could also provide your expected result column.

 

The textual description is easily flawed, like what do you mean by CODE and LINE columns?

  • charlito's avatar
    charlito
    New Member
    3 years ago

    FreemanZ 

    i've already edited the original post. Maybe could be more clear now

    • FreemanZ's avatar
      FreemanZ
      Icon for Super User rankSuper User
      3 years ago

      hi charlito 

      not sure if i fully get you, please try to add a calculated column in values table like:

      SEC = 
MAXX(
    FILTER(
        master,
        master[KEY]='values'[KEY]
            &&master[BND]='values'[BND]
            &&master[START_HOUR]<='values'[HOUR]
            &&master[END_HOUR]>='values'[HOUR]
    ),
    master[SEC]
)

      it worked like:

      • charlito's avatar
        charlito
        New Member
        3 years ago

        FreemanZ 

        for some reason it is not working for me like you showed

        KEYBNDHOURACT
        REPS818/04/23 5:35 pm 
        REPS818/04/23 6:34 am 
        ZATR319/04/23 2:30 am 

         

        It is not considering the day (18/04/23) as part of the formula, it is only taking the hour.
        So for me it is not retrieving a value for ZATR row.

        So when day changes in master from 18 to 19, it is not considering that 2:30 am is in that range, because ot only takes the hour, so

         

        KEYBNDSECSTART_HOUREND_HOUR
        REPS8S218/04/23 5:00 pm19/04/23 2:00 am
        REPS8S118/04/23 6:00 am18/04/23 5:00 pm
        ZATR8S318/04/23 9:00 pm19/04/23 4:00 am
        ZATR8S118/04/23 8:00 am18/04/23 7:00 pm
        MAKI3S218/04/23 9:00 pm19/04/23 4:00 am
        QWER2S318/04/23 5:30 am18/04/23 1:30 pm
        ZATR3S218/04/23 10:00 pm19/04/23 5:00 am

         

        2 is outside from :  22 <= 2 & 5 >= 2, clearly 22 is not less than 2. 

         

        So,tThe day is not being considered.

        😞