Forum Discussion
New Column Based on Other Column
Dear Friends,
I have below data.
| Essential or Non Essential | Date | Cafe & Date & Item Code | Stock Status |
| ESSENTIAL | 19-Apr-21 | 1430070074/19/2021A2338 | Not Available |
| ESSENTIAL | 19-Apr-21 | 1430070074/19/2021A2338 | Stock Available |
| NON ESSENTIAL | 19-Apr-21 | 1430070074/19/2021A2338 | Not Available |
| ESSENTIAL | 19-Apr-21 | 1430070074/19/2021W443 | Not Available |
| NON ESSENTIAL | 19-Apr-21 | 1430070074/19/2021W443 | Not Available |
| ESSENTIAL | 19-Apr-21 | 1430070074/19/2021W443 | Not Available |
| NON ESSENTIAL | 19-Apr-21 | 1430070074/19/2021W443 | Not Available |
I want to arrive new column based on "Cafe & Date & Item Code" & "Stock Status" & "Essential or Non Essential" column i..e. "New Status" column.
| Essential or Non Essential | Date | Cafe & Date & Item Code | Stock Status | New Status |
| ESSENTIAL | 19-Apr-21 | 1430070074/19/2021A2338 | Not Available | Not Available |
| ESSENTIAL | 19-Apr-21 | 1430070074/19/2021A2338 | Stock Available | Not Available |
| NON ESSENTIAL | 19-Apr-21 | 1430070074/19/2021A2338 | Not Available | Not Available |
| ESSENTIAL | 19-Apr-21 | 1430070074/19/2021W443 | Not Available | Not Available |
| NON ESSENTIAL | 19-Apr-21 | 1430070074/19/2021W443 | Not Available | Not Available |
| ESSENTIAL | 19-Apr-21 | 1430070074/19/2021W443 | Not Available | Not Available |
| NON ESSENTIAL | 19-Apr-21 | 1430070074/19/2021W443 | Not Available | Not Available |
Column "Cafe & Date & Item Code" contains duplicate data and if "Essential or Non Essential" is "Essential" and "Stock Status" is "Not Avilable" and for any one of the "Essential" of "Cafe & Date & Item Code" column then "New Status" should be "Not Available" only.
For an example take a look at the first 3 rows.
| Essential or Non Essential | Date | Cafe & Date & Item Code | Stock Status | New Status |
| ESSENTIAL | 19-Apr-21 | 1430070074/19/2021A2338 | Not Available | Not Available |
| ESSENTIAL | 19-Apr-21 | 1430070074/19/2021A2338 | Stock Available | Not Available |
| NON ESSENTIAL | 19-Apr-21 | 1430070074/19/2021A2338 | Not Available | Not Available |
In this first two are essential but for first row "Stock Status" is "Not Available" and for second row "Essential" and stock status is "Stock Available", since the first one is already "not available" then this also should be "not available" only.
Minakshi Anonymous Jihwan_Kim amitchandak @parry2k @Geradav @PhilipTreacy @Kinjal @Sujit_Thakur
Anonymous , Try a new column like
new column =
var _cnt = countx(filter(Table, [Cafe & Date & Item Code] =earlier([Cafe & Date & Item Code]) && [Essential or Non Essential] = "ESSENTIAL" && [Stock Status] = "Not Available" ), [Cafe & Date & Item Code] ) +0
return
if( _cnt >0 , "Not Available" ,[Stock Status])
2 Replies
Anonymous , Try a new column like
new column =
var _cnt = countx(filter(Table, [Cafe & Date & Item Code] =earlier([Cafe & Date & Item Code]) && [Essential or Non Essential] = "ESSENTIAL" && [Stock Status] = "Not Available" ), [Cafe & Date & Item Code] ) +0
return
if( _cnt >0 , "Not Available" ,[Stock Status])Thanks you so much amitchandak it worked wonders for me.