Add data row based on Retail value check

[SunnyJha]

Hi, I have a datatable dt and based on some date conditions I need to add the rows with updated values to a new datatable.

Input dt:

Output dt:

Also please refer the attached file for the whole input & output sample.
Test1.xlsx (11.8 KB)

The output dt is based on how many time the input dt has a particular retail repeated consecutively. For P1, the first 3 rows are blank so skip as we dont need those. Now retail is present in 5th row and is same till row 7 so one datarow should be added to the output dt with the start date from row 5 and end date from row 7.

@Yoichi @ppr

[Jason.Li]

hi~

you can use “filter data table”, reference is as follows
1.

3.

Main.xaml (3.2 KB)

[SunnyJha]

If you see the dates the values are updated as per how many consecutive times a retail value is repeated. We have to incorporate that logic as well.

[Yoichi]

HI,

How about the following sample?

Sample
Sample20240619-3.zip (14.5 KB)

Regards,

[SunnyJha]

Thanks Yoichi! Helpful as always

[SunnyJha]

Hi Yoichi,
The last row if it has a retail with Start Date 12/26/2023 & End date 1/1/2024 gets truncated in the output. Not sure why. All other logic seems fine and working.

[Yoichi]

Hi,

Sorry, it seems the above sample was not considered about the last row. Can you try the following ?

Sample20240619-3 (2).zip (15.7 KB)

Regards,

[SunnyJha]

thanks! this worked.