topic Re: Generate next value based on the previous value in App Development

Generate next value based on the previous value

waleeed_mahmood — Sat, 16 Nov 2024 19:17:01 GMT

Hello everyone,

I have the following issue if anyone can help with it, please.

i have a list of PCK numbers: as shown in table below. I want to generate something similar to the table below. My only issue is the first column. Suppose that i have the first number, how do i use the previous rows number and add 10 to it to make the next number. So, for example, my first number is 40 and for the first row i use 40+10=50. For the next iteration, i want to use 50+10=60 and so on. It is like iterative calculation using the value i pass in.

the number 40 is taken from another table. It is the max "Sort Number" in another table. so I use Peek() to obtain the max number from that table. Now i want to generate a separate table with the PCK list i have.

Please let me know if you are unsure of what i am trying to ask.

SortNumber	PN	Indicator	PCK	GCK
50	*	*	R444	*
60	*	*	T3233	*
70	*	*	Y351	*
80	*	*	R2331	*

Thank you.

Re: Generate next value based on the previous value

gavinlaird — Fri, 17 Jan 2020 17:31:07 GMT

Set vFirstNumber = the max "Sort Number" from another table, then use this script:

LOAD PCK ,if(RowNo()=1,$(vFirstNumber),Peek('SortNumber'))+10 as SortNumber Resident [your data];

It uses RowNo() to check if it is loading the first row. If so, it uses the vFirstNumber variable you set earlier (should be 40). After the first row, the table will load the previous SortNumber instead of using vFirstNumber.

Re: Generate next value based on the previous value

waleeed_mahmood — Fri, 17 Jan 2020 21:32:03 GMT

Hey Gavin,

Thank you for the quick reply. I just have one question

if(RowNo()=1,$(vFirstNumber),Peek('SortNumber'))+10 as SortNumber

in the above code, you have Peek('SortNumber' ) and you are also assigning the column "as SortNumber". are you referencing the same column? or in different words, are both SortNumber colored red the same thing?

Also, i tried it and it is giving me null values

Re: Generate next value based on the previous value

gavinlaird — Fri, 17 Jan 2020 21:32:10 GMT

Yes, it is referencing the same "SortNumber" column. That is why the first part of the expression is required- "if(RowNo()=1" checks to see if it is loading the first row (in which case, there would be no "SortNumber" field yet to reference with Peek). For the first row, it uses $(vFirstNumber), rather than Peek('SortNumber'). For all rows after that, it uses Peek('SortNumber') since the "SortNumber" field exists at that point.

Re: Generate next value based on the previous value

waleeed_mahmood — Fri, 17 Jan 2020 21:34:54 GMT

I am getting the following result using your code

Re: Generate next value based on the previous value

gavinlaird — Fri, 17 Jan 2020 21:48:28 GMT

I can troubleshoot if you post your load script

Re: Generate next value based on the previous value

andoryuu — Fri, 17 Jan 2020 22:33:17 GMT

Another would be to have your load script this way assuming you have your starting number assigned to a variable called "vStartingNum". I tested this in September 2019 release and it returns 6 rows with IDs 50-100 progressing by 10s.

Table1:
LOAD *
INLINE [
SomeNumber
40
];

let vStartingNum = Peek('SomeNumber', 0, 'Table1');

Trace $(vStartingNum);

NoConcatenate
Table2:
LOAD *
INLINE [
SomeOtherData
asdfa
sdfghdhrr
mrsrm3
mrytdmth
mydf
mhgmdgh
];

NoConcatenate
Table3:
Load SomeOtherData, BaseNumber+(RowNo()*10) as Rownumber;
LOAD SomeOtherData, '$(vStartingNum)' as BaseNumber
Resident Table2;

Drop Table Table2;

Re: Generate next value based on the previous value

waleeed_mahmood — Fri, 17 Jan 2020 22:53:59 GMT

let vSortNumber = Peek('Sort Number',-1,'Derivation_Rule_Table'); //8640 Temp: Load *, if(RowNo()=1,$(vSortNumber),Peek('SortNumber'))+10 as SortNumber, ; Load Distinct(FGCK) as GCKs //there are only 9 distinct GCK values Resident Material_Master_Table Where IsNull(LINKTORULE) OR Len(Trim(LINKTORULE))=0;

The result of this is as follows:

it took away the distinct GCKs and duplicated it over and over again(thousands of records). I am expecting only 9 rows starting from 8650 til 8740 as sort number since there are only 9 distinct GCKs. the above snippet actually starts from 8650 til 25480 (shown above)

Re: Generate next value based on the previous value

Kushal_Chawda — Sat, 18 Jan 2020 11:15:57 GMT

would you be able to share sample data?

Re: Generate next value based on the previous value

waleeed_mahmood — Sat, 18 Jan 2020 22:59:58 GMT

Hello Kush,

for sure. I have the following GCK values:

and what is want is the following:

For each of the GCK values, i want ONE ROW only. with the values similar to above ( which i can hard code into the script). the only issue is getting a number that is the incremented by 10 from the previous value. So, in the case above, first value is 8650, 8660,8670,8680 and so on.

Re: Generate next value based on the previous value

waleeed_mahmood — Sat, 18 Jan 2020 23:01:35 GMT

Hello Andoryuu:

I will apply your code to my script and let you know. So far, it looks promising. 🙂