Get the last value of 2 fields

[bsbernabe]

Hello There,

I have concern regarding to get the last value of BIN NO. and AMOUNT,

i already use LASTVALUE([BIN NO.]) to get the last value and also Previous([SERIAL NO]),Previous([AMOUNT]),[AMOUNT]) to get the amount some is ok but some is not ok in base on my validation is it possible to get the last value of this two fields?

This is the Data

ID	DATE	ITEM NO.	BIN NO.	SERIAL NO.	AMOUNT
544259	1/2/2018	101138	G123XYZ	KSW10E012345	42304.45
544772	1/2/2018	101138	G123WAP	KSW10E012345	42321.3
622120	2/26/2018	101138	G123WAP	KSW10E012345
622120	2/26/2018	101138	G213HDC	KSW10E012345

This is the output that i need

ID	DATE	ITEM NO.	BIN NO.	SERIAL NO.	AMOUNT
622120	2/26/2018	101138	G213HDC	KSW10E012345	42321.3

Best Regards,

Bing

[sunny_talwar]

And you need this to be done in the script or front end?

[bsbernabe]

Hi Sunny,

Can do any of this way script or front end as long as i can get the output that i need.

Best Regards,

Bing

[sunny_talwar]

Something like this

Table:

LOAD ID,

DATE,

[ITEM NO.],

[BIN NO.],

[SERIAL NO.],

If(Len(Trim(AMOUNT)) = 0, Peek('AMOUNT'), AMOUNT) as AMOUNT;

LOAD * INLINE [

    ID, DATE, ITEM NO., BIN NO., SERIAL NO., AMOUNT

    544259, 1/2/2018, 101138, G123XYZ, KSW10E012345, 42304.45

    544772, 1/2/2018, 101138, G123WAP, KSW10E012345, 42321.3

    622120, 2/26/2018, 101138, G123WAP, KSW10E012345,

    622120, 2/26/2018, 101138, G213HDC, KSW10E012345

];

Will transform your data to this

And now you can display your last row which will have the right AMOUNT.

HTH

Best,

Sunny

[shiveshsingh]

T: LOAD ID, DATE, [ITEM NO.], [BIN NO.], [SERIAL NO.] , if(len(Trim(AMOUNT))=0,Peek(AMOUNT),AMOUNT) as AMOUNT ;

F:load * INLINE [

    ID, DATE, ITEM NO., BIN NO., SERIAL NO., AMOUNT

    544259, 1/2/2018, 101138, G123XYZ, KSW10E012345, 42304.45

    544772, 1/2/2018, 101138, G123WAP, KSW10E012345, 42321.3

    622120, 2/26/2018, 101138, G123WAP, KSW10E012345,

    622120, 2/26/2018, 101138, G213HDC, KSW10E012345

];