How to load the lastest non-blank figure?

[My_Rebecca]

How to load the lastest non-blank figure by expression?

eg:

if today() is 3/27/2023, the result is 3650;

if today() is 4/3/2023, the result is 1660;

if today() is 4/6/2023, the result is 4685；

if today() is 4/10/2023, the result is 6685.

[sandeep-singh · Accepted solution]

Hi @My_Rebecca, The field was created in the preceding load. I have created a dummy script to give you an idea. 

Script:

Data load progress:

Output: 

[My_Rebecca]

Dear @sandeep-singh , many thanks to your great support.

But I check my demand again, and I find indeed I need the whole row data of the latest number, so I want to know if there is expression similar to "Row()" in excel or some other ideas to solve the issue.

[sandeep-singh]

Hi @My_Rebecca, to update whole row, you need to use the peek function for each field. You can use the below script on your base table. I have created a script for two fields, you can copy paste and update for others fields. 

if(Len(Trim([Total DHL DN line]))=0, peek('New_Total DHL DN line'),[Total DHL DN line]) as [New_Total DHL DN line],
if(Len(Trim([Online PGI Line]))=0, peek('New_Online PGI Line'),[Total DHL DN line]) as [New_Online PGI Line],

[My_Rebecca]

Dear @sandeep-singh , there is a mistake: column "DN cancelled (Request By BA)" is blank at more higher row, so the your idea will not work regarding to these blank cell.

I think it should be like this:

if(Len(Trim([Total DHL DN line]))=0, peek('New_DN cancelled (Request By BA)'),[DN cancelled (Request By BA)]) as [New_DN cancelled (Request By BA)]

How about this?