topic How to get max date from formatted text date in QlikView

How to get max date from formatted text date

Sun, 15 Nov 2015 07:26:07 GMT

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

Re: How to get max date from formatted text date

ecolomer — Sun, 15 Nov 2015 07:44:26 GMT

Can you upload excel file?

First, try to format column of Date as Date Format

Re: How to get max date from formatted text date

maxgro — Sun, 15 Nov 2015 08:53:21 GMT

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)

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]);

Re: How to get max date from formatted text date

HirisH_V7 — Sun, 15 Nov 2015 08:56:20 GMT

Hi,

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

Thanks,

hirish

Re: How to get max date from formatted text date

maxgro — Sun, 15 Nov 2015 10:58:38 GMT

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]);

Re: How to get max date from formatted text date

Anonymous — Mon, 16 Nov 2015 07:10:26 GMT

try Alt function