Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Not applicable

How to get max date from formatted text date

Hello,

See attached qv file. I have a mix field of both text and date in the field i wanna format to a standard date to pick the max(Date) i.e the latest date data only. from the newly formated date field. See QVW

Thanks

Ema

5 Replies
ecolomer
Honored Contributor II

Re: How to get max date from formatted text date

Can you upload excel file?

First, try to format column of Date as Date Format

MVP
MVP

Re: How to get max date from formatted text date

it seems the problem is you have this date format

SET DateFormat='M/D/YYYY';

but the date in excel is also in DD/MM/YYYY format (13/11/2015 DD/MM/YYYY)

1.png

you can try to change the date format

SET DateFormat='DD/MM/YYYY';



or without change in the SET DateFormat

use the date functions (Date and Date#) in the load; if you post the excel I could check

LOAD

     [S/N],

     Date as OldDate,

     Date(alt(num(Date), Date#(Date, 'DD/MM/YYYY'))) as Date,

     Group,

     Target,

     Actual

FROM (ooxml, embedded labels, table is [ summary]);

HirisH_V7
Honored Contributor

Re: How to get max date from formatted text date

Hi,

Please check the attachment. (Max date on list-box)

Thanks,

hirish

HirisH
“Aspire to Inspire before we Expire!”
MVP
MVP

Re: How to get max date from formatted text date

NewSummary:

NoConcatenate

LOAD [S/N],

  Date as OldDate,

     Date(alt(num(Date), Date#(Date, 'DD/MM/YYYY'))) as Date,

     Group,

     Target,

     Actual

FROM

[summary II - Copy.xlsx] (ooxml, embedded labels, table is [ summary]);

1.png

aaryanqvdevelop
Contributor

Re: How to get max date from formatted text date

try Alt function