Script calculation based on previous values

antonopn — Mon, 07 Mar 2022 13:52:15 GMT

Hi everyone,

I am facing a problem with a table I am reading. Table has some average weight ranges, but has only "From" values as below. I would like to calculate "To" column inside the script. Columns "TableID" and SizeID are also coming from the table and I need them.

TableID	From	To	SizeID	Calcuation Notes
{3D643767}	900	*99999*	4	TableID Start -> static value 99999
{3D643767}	900	*99999*	5	TableID Start -> static value 99999
{3D643767}	900	*99999*	6	TableID Start -> static value 99999
{3D643767}	800	*899,999*	4	From changes, has to be (previous(From)-0.001)
{3D643767}	800	*899,999*	5	From remains, Previous(To)
{3D643767}	800	*899,999*	6	From remains, Previous(To)
{3D643767}	700	*799,999*	3	From changes, has to be (previous(From)-0.001)
{3D643767}	700	*799,999*	4	From remains, Previous(To)
{3D643767}	700	*799,999*	5	From remains, Previous(To)
{3D643767}	700	*799,999*	6	From remains, Previous(To)
{3D643767}	600	*699,999*	2	From changes, has to be (previous(From)-0.001)
{3D643767}	600	*699,999*	3	From remains, Previous(To)
{3D643767}	600	*699,999*	4	From remains, Previous(To)
{3D643767}	600	*699,999*	5	From remains, Previous(To)
{89454FAA}	1200	*99999*	4	TableID Start -> static value 99999
{89454FAA}	1200	*99999*	5	TableID Start -> static value 99999
{89454FAA}	1200	*99999*	6	TableID Start -> static value 99999
{89454FAA}	800	*1199,999*	4	From changes, has to be (previous(From)-0.001)
{89454FAA}	800	*1199,999*	5	From remains, Previous(To)
{89454FAA}	800	*1199,999*	6	From remains, Previous(To)
{89454FAA}	700	*799,999*	3	From changes, has to be (previous(From)-0.001)
{89454FAA}	700	*799,999*	4	From remains, Previous(To)
{89454FAA}	700	*799,999*	5	From remains, Previous(To)
{89454FAA}	700	*799,999*	6	From remains, Previous(To)

I tried some combinations of "if" with "previous" but did not work. Could someone please help?

Thank you in advance for devoting your time

Re: Script calculation based on previous values

MarcoWedel — Mon, 07 Mar 2022 20:41:13 GMT

maybe like this?

table1: LOAD TableID, From, If(TableID=Previous(TableID),If(From=Previous(From),Peek(To),Previous(From)-0.001),99999) as To, SizeID FROM [https://community.qlik.com/t5/QlikView-App-Dev/Script-calculation-based-on-previous-values/td-p/1901845] (html, codepage is 1252, embedded labels, table is @1);

Re: Script calculation based on previous values

antonopn — Tue, 08 Mar 2022 09:22:55 GMT

@MarcoWedel you are great. I learned the function 'Peek'. Many thanks!

topic Re: Script calculation based on previous values in QlikView

Script calculation based on previous values

Re: Script calculation based on previous values

Re: Script calculation based on previous values