Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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?
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.
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;
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.
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;
I will try and let me know how it goes, Thanks.
Sorry, Let you Know*