topic Re: How to find next value of the field at the script level in QlikView

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

farheenayesha — Fri, 07 Dec 2018 14:50:36 GMT

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

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

sunny_talwar — Fri, 07 Dec 2018 15:35:40 GMT

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;

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

farheenayesha — Fri, 07 Dec 2018 17:38:15 GMT

You are Genius Sunny!! Thank you so much.

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

farheenayesha — Fri, 07 Dec 2018 18:03:58 GMT

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.

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

sunny_talwar — Fri, 07 Dec 2018 18:08:19 GMT

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;

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

farheenayesha — Fri, 07 Dec 2018 18:14:59 GMT

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