Custom column based on 2 different columns

[anandmitta]

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

[amitchandak · Accepted solution]

@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)

[amitchandak · Accepted solution]

@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 · Accepted solution]

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)