Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Max Date from Date field which is in number format

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

1 Reply
Highlighted
MVP
MVP

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;