Forum Discussion

anandmitta's avatar
anandmitta
Helper I
4 years ago
Solved

Custom column based on 2 different columns

Hello community, please excuse me if this scenario has already been answered.   I have a table with 4 columns. ID BookNo Cont No Created date 1234 1111 1222 22-Feb-22 2345 2222   ...
  • amitchandak's avatar
    4 years ago

    anandmitta , Create a new column like

    New column =

    var _diff = datediff([created_date], today(), day)+1

    return

    Switch(true() ,

    _diff <7, 0 ,

    _diff <= 12 && isblank([Book No]) && isblank([Cont No])   , 0,

    _diff <= 12   , 1,

    _diff > 12 && isblank([Book No]) && isblank([Cont No])   , 0,

    _diff <= 12   , 2,

    0)

  • anandmitta's avatar
    anandmitta
    4 years ago

    Thank you so much amitchandak. It worked 

     

    There was just 1 small correction to the above query. The value should be 1 or 2 if it falls in the specific date range and book no and cont no are empty. where as the above query makes it 0. Below is the corrected query.

     

    New column =

    var _diff = datediff([created_date], today(), day)+1

    return

    Switch(true() ,

    _diff <7, 0 ,

    _diff <= 12 && isblank([Book No]) && isblank([Cont No])   , 1,

    _diff <= 12   , 0,

    _diff > 12 && isblank([Book No]) && isblank([Cont No])   , 2,

    _diff <= 12   , 0,

    0)