Max of Max in Script

[prathipsrinivas]

Hi All,

I would like to figure out maxstring(TransNo) in a Max(Entrydate) for a particular claim in Script.

Please suggest me how to implement it.

The idea is to set flag when When max of transactionNo and max entrydate record comes.

Thanks,

Prathip

[sunny_talwar]

So, each Entrydate can have multiple TransNo and you only want to flag the Max TransNo from the Max Entrydate?

EntryDate     TransNo

10/15/2018     1

10/15/2018     2

10/15/2018     3

10/16/2018     2

10/16/2018     3

In the above data, you would want to only flag the last line?

[prathipsrinivas]

Exactly Sunny, and in Addition if there is currency field there, we would have to do the same for separate Currencies.

Like:

EntryDate     TransNo  Currency  flag

10/15/2018     1             A  

10/15/2018     2            A

10/15/2018     3            A               1

10/16/2018     2            B

10/16/2018     3            B               1

The result should be like mentioned above.

[sunny_talwar]

May be something like this

Table:

LOAD EntryDate,

     TransNo,

     Currency

FROM ....;

Left Join (Table)

LOAD Currency,

     Max(EntryDate) as EntryDate,

     FirstSortedValue(TransNo, -(EntryDate + TransNo)) as Recent

Resident Table;

FinalTable:

LOAD EntryDate,

     TransNo,

     Currency,

     If(Recent = TransNo, 1) as flag

Resident Table;

DROP Table Table;

[prathipsrinivas]

Thanks Sunny but the catch here is transaction number is in mixed format, sometimes it is numbers and sometimes it is Alphanumeric.

I am not Sure whether the above approach would be suitable in this case.

Let me know if am wrong.

Thanks.

[sunny_talwar]

So, in that case, may be this

Table:

LOAD EntryDate,

     TransNo,

     Currency

FROM ....;

Left Join (Table)

LOAD Currency,

     Max(EntryDate) as EntryDate,

     FirstSortedValue(TransNo, -(EntryDate - Rank(TransNo))) as Recent

Resident Table;

FinalTable:

LOAD EntryDate,

     TransNo,

     Currency,

     If(Recent = TransNo, 1) as flag

Resident Table;

DROP Table Table;

[prathipsrinivas]

I will try and let me know how it goes, Thanks.

[prathipsrinivas]

Sorry, Let you Know*