Linq query to update End date with next line start date -1

Help Studio
1

Update the date which has default year 9999 from next line item start date - 1
PFB

INPUT
STRT_DT END_DT
30-Aug-2020 25-Dec-9999
22-Nov-2020 14-Apr-2021
30-Aug-2020 25-Dec-9999
28-Nov-2020 14-Apr-2021
OUTPUT
STRT_DT END_DT UPD DT
30-Aug-2020 25-Dec-9999 21-Nov-2020
22-Nov-2020 14-Apr-2021
30-Aug-2020 25-Dec-9999 27-Nov-2020
28-Nov-2020 14-Apr-2021
2

@ppr Hi bro any idea regarding this

3

@Aravinthan,

You can use For Each row activity like below,
Read excel and store in DT
For each row in DT{
if row(“END_DT”).ToString.Contains(“9999”) {
// write your date that you want to write by using write cell activity
// provide appropriate index to range of this activity i.e C2
image

}
}

4

There will be more than 5000 rows so only trying for linq query

5

input:
grafik

Flow:
grafik

Add DataColumn:
grafik

Add DataRow:
grafik
dtData.AsEnumerable.Last().ItemArray.Append(nothing).toArray

LINQ:

(From i In Enumerable.Range(0,dtData.Rows.Count-1)
Let row = dtData.Rows(i)
Let chk =  CDate(row("END_DT").toString.Trim()).Year = 9999
Let udt = CDate(dtData.Rows(i+1)("STRT_DT").toString.Trim()).AddDays(-1).ToString("dd-MMM-yyyy")
Let upd = If(chk,udt,"")
Let ra = row.ItemArray.Append(upd).ToArray
Select dtResult.Rows.add(ra)).CopyToDataTable

Output:
grafik

Depending on the data from input, maybe CDate will be replaced by another Conversion function in case of issues

2 Likes
7

Yes you are correct getting object reference issue due to Date. Please find below screenshot input date format
image

8

grafik
So it is parsing

ensure

  • dtResult Variable is intialized (dtResult = dtData.Clone)
  • dtData is similar to the post and has only 2 cols

find starter help here:
ForEachVar_ReactOnPrevRow.xaml (8.4 KB)

2 Likes
9

Thanks bro worked like king :slight_smile: :star_struck:

10

Perfect, just do your final testing and afterwards please mark the solving post as solution. Thanks

1 Like
11

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.