How to replace a null value with existing column value

[shabarish0587]

HI Folks,

                I am facing one issue. My requirement is i have few columns like ID, Team, Start Date, End Date, Iteration and PlanV. from this data few teams data have null. i want to replace that null Value with existing Coloumn Value.

For Eg: if we take Team as T1. from my sample file we have three T1s are there under that two have values for start date and end date and also Values like 60 and 75. but for second row of T1 has null data for start date and end date and also null for  PlanV, so here i need to replace with this null value for PlanV with 60 why i need 60 here is i need take latest date value for that PlanV. if we observe another example T4 we need to take value as 65 in PlanV in the place of null.

Given input:

ID	Team	Start Date	End Date	PlanV	Iteration
1	T1	3/1/2021	18/01/2021	60	Iter1
2	T1	-	-	0	-
3	T1	1/12/2020	20/12/2020	75	Iter2

Expected OutPut:

ID	Team	Start Date	End Date	PlanV	Iteration
1	T1	3/1/2021	18/01/2021	60	Iter1
2	T1	NA	NA	60	NA
3	T1	1/12/2020	20/12/2020	75	Iter2
1	T4	3/2/2021	20/03/2021	80	Iter1
2	T4	NA	NA	65	NA
3	T4	4/3/2021	20/04/2021	65	Iter2

Can anyone please help me on this. I am sharing sample file. Thanks in Advance

[edwin · Accepted solution]

first is to get the replacement per team, just aggregate by max(EndDate), then either use applymap or as i would prefer inner join with your table.  then unfortunately you need to process your whole table to replace the missing field:

data:
load ID,Team,[Start Date],[End Date],num(PlanV) as PlanV,Iteration inline [
ID,Team,Start Date,End Date,PlanV,Iteration
1,T1,3/1/2021,18/01/2021,60,Iter1
2,T1
3,T1,1/12/2020,20/12/2020,75,Iter2
1,T4,3/2/2021,20/03/2021,80,Iter1
2,T4
3,T4,4/3/2021,20/04/2021,65,Iter2
];


t1: load Team, date(max([End Date])) as [End Date] Resident data where not isnull([End Date]) group by Team;

inner join (t1) load Team, [End Date], PlanV Resident data ;

inner join (data) load Team, PlanV as LastPlanV Resident t1;

NoConcatenate NewData: load ID,Team,[Start Date],[End Date], if(isnull(PlanV), LastPlanV, PlanV) as PlanV,Iteration Resident data;

drop table t1, data;

hope that helps