Max Date from Date field which is in number format

[prathipsrinivas]

Hi Again,

I want to get the Max of date from the date field which is in Number Format.

I was managed to convert it in to required format (DD/MM/YYYY).

The issue here is the original date (number) field which has multiple entries for single date but with different timings like

22/10/2018 10:28

22/10/2018  09:45

22/10/2018  11:20

and has a different Transactions aligned to the  each Entry Date.

Example:-

EntryDate                              Trans

22/10/2018 10:28                   12345

22/10/2018  09:45                  13456

22/10/2018  11:20                   11123

here, I want to set flag at Maximum Transaction. i.e at the 2 row.

I want to just consider date not timings and have to set flag at maximum transaction.

Need suggestions to achieve.

Thanks,

Prathip

[swuehl]

Maybe something like

Set DateFormat ='DD/MM/YYYY';

Data:

LOAD

     Dayname(DatenNumberField) as Date,

     Timestamp(DayNumberField) as Timestamp,

     Transaction

FROM YourTable;

LEFT JOIN (Data)

LOAD Date, Max(Transaction) as Transaction, 1 as MaxTransactionFlag

RESIDENT Data

GROUP BY Date;

edit: or if you want to flag the max transaction only for the max date:

...

LEFT JOIN(Data)

LOAD Maxstring(Date) as Date, FirstsortedValue(Transaction, -(Date+Transaction / 1E6) ) as Transaction, 1 as MaxTransactionFlag

RESIDENT Data;