Case 002 How to display the latest date amount using FirstSortsValaue = 933,824.35

[paulyeo11]

Hi All

I have below load script :-

GL_TABLE:
LOAD
'PM' as SOURCE,
[GL Code] as GL_CODE_,
If([GL Code]>=2001010 and [GL Code]<=2001010,'sTOCK_') as [sTOCK_],
Date( Date#([Posting Date], 'MM/DD/YYYY'), 'DD/MM/YYYY') as [date],
[C/D]*-1 as Amount,
[Cumulative Balance (LC)] as Amount_S
FROM
$(vRAWPath)$(vFile201)
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Table Box list below :-

GL_CODE_ ----- Amount_S ----- date
2001010 ----- 933,824.35 ----- 24/04/2020
2001010 ----- 933,630.83 ----- 24/04/2020
2001010 ----- 933,622.48 ----- 24/04/2020
2001010 ----- 933,585.44 ----- 24/04/2020
2001010 ----- 933,577.09 ----- 24/04/2020

I need to display the latest date amount using FirstSortsValaue , in order to display 933,824.35 :-

Expression :-
=FirstSortedValue(Amount_S,-date)

But i get null value , not sure where go wrong ? Hope some one can advise me.

Paul

[Anil_Babu_Samineni]

Can you simply try?

Max(Amount_S)

[paulyeo11]

Hi Sir

I just try your expression it return max value= 1,173,155 Which is not correct.

Paul

[Anil_Babu_Samineni]

FirstSortedValue works on aggregated data, But your's showing non-aggregate data? If you want Max date of the value. Try

Max({<date={$(=Date(Max(date),'DD/MM/YYYY'))}>} Amount_S)

[paulyeo11]

Mr Anil

I just try your new expression, I still get null.

[kumaranvr1984]

Hi,

Do you load any other field which contains time or row id something like that?

[paulyeo11]

Hi Sir

it work fine when i create a sample raw data file , it dont have any issue. When i apply actual data to it got issue , because in between the row have other items beside stock.  By the way  i already use filter all stock item by GL_CODE.  

Paul Yeo 

[Saravanan_Desingh]

This might be failing because, you have repeated date values. May be try like:

=FirstSortedValue(Amount_S,-date+Amount_S)

This will make the date Field unique.