Forum Discussion
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 | 26-Mar-22 | |
| 3456 | 3333 | 3444 | 3-Apr-22 |
| 4567 | 12-Mar-22 | ||
| 5678 | 25-Mar-22 | ||
| 6789 | 7666 | 2-Feb-22 | |
| 7890 | 4444 | 5645 | 31-Jan-22 |
Above is the sample data.
I want a custom column with values 0, 1, 2 with the respective below conditions.
1) If the date difference is less than 7 days between created date and current date then the custom column should be '0'.
2) If the date difference is 7-12 days between created date and current date, and either of BookNo or ContNo is empty then custom column value should be '1'. If BookNo and ContNo is not empty then '0'.
3) If the date difference is greater than 12 days between created date and current date, and either of BookNo or ContNo is empty then custom column value should be '2'. If BookNo and ContNo is not empty then '0'.
Below is the expected result
Current date: 4-Apr-22
| ID | BookNo | Cont No | Created date | Custom Column |
| 1234 | 1111 | 1222 | 22-Feb-22 | 0 |
| 2345 | 2222 | 26-Mar-22 | 1 | |
| 3456 | 3333 | 3444 | 3-Apr-22 | 0 |
| 4567 | 12-Mar-22 | 2 | ||
| 5678 | 25-Mar-22 | 1 | ||
| 6789 | 7666 | 2-Feb-22 | 2 | |
| 7890 | 4444 | 5645 | 31-Jan-22 | 0 |
I have been trying to do this and I'm getting error when trying to create a custom column based on 2 column values.
Please help me with this.
Thanks & Regards,
Anand
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)
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)
2 Replies
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)
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)