How to find next value of the field at the script level

[farheenayesha]

Hi,

Please help to find next value of the field at the script level. in the below table 4th July is an holiday and hence i want the same value as the next row that is 3 not as 2. Please help me to get the next value from the field for the holidays.

Nth day by Month_CY as 3 for 7/4/2018

Nth day by Qtr_CY as 3 for 7/4/2018

Nth day by Year_CY as 130  for 7/4/2018

[sunny_talwar · Accepted solution]

Try this at the end of the load

FinalWeekBased445:
LOAD *,
	 If(MonthStart = Previous(MonthStart),
	 	If(HolidayFlag = 0, [Nth Day by Month_CY], Peek('Nth Day by Month_CY_Temp')), [Nth Day by Month_CY]) as [Nth Day by Month_CY_Temp],
	 If(QuarterStart = Previous(QuarterStart),
	 	If(HolidayFlag = 0, [Nth Day by Qtr_CY], Peek('Nth Day by Qtr_CY_Temp')), [Nth Day by Month_CY]) as [Nth Day by Qtr_CY_Temp],
	 If(YearStart = Previous(YearStart),
	 	If(HolidayFlag = 0, [Nth Day by Yr_CY], Peek('Nth Day by Yr_CY_Temp')), [Nth Day by Month_CY]) as [Nth Day by Yr_CY_Temp]
Resident WeekBased445
Order By [Invoice Date] desc;

DROP Table WeekBased445;

[farheenayesha]

You are Genius Sunny!! Thank you so much.

[farheenayesha]

Hi Sunny,

 Just wanted to understand, why NTh_Qrt_CY_Temp and NTh_YR_CY_Temp are showing as 25 for Sat and Sun at the end. Please find the snapshot below.

[sunny_talwar · Accepted solution]

Because of a typo that I made

FinalWeekBased445:
LOAD *,
	 If(MonthStart = Previous(MonthStart),
	 	If(HolidayFlag = 0, [Nth Day by Month_CY], Peek('Nth Day by Month_CY_Temp')), [Nth Day by Month_CY]) as [Nth Day by Month_CY_Temp],
	 If(QuarterStart = Previous(QuarterStart),
	 	If(HolidayFlag = 0, [Nth Day by Qtr_CY], Peek('Nth Day by Qtr_CY_Temp')), [Nth Day by Qtr_CY]) as [Nth Day by Qtr_CY_Temp],
	 If(YearStart = Previous(YearStart),
	 	If(HolidayFlag = 0, [Nth Day by Yr_CY], Peek('Nth Day by Yr_CY_Temp')), [Nth Day by Yr_CY]) as [Nth Day by Yr_CY_Temp]
Resident WeekBased445
Order By [Invoice Date] desc;

[farheenayesha]

Got it. This is perfect. Thank you so much 🙂