topic Re: Max Date in QlikView

Max Date

Tue, 27 Aug 2013 03:00:34 GMT

Hi All,

I have date filed with name ReportDate which is in format of 3/22/2013 2:20:45 PM

I need to load only max date from ReportDate in application . When i try max(ReportDate) i get an error

Please find attched sourcefile and QVW .

Thanks

Re: Max Date

rajeshvaswani77 — Tue, 27 Aug 2013 03:20:12 GMT

Hi Srinu,

Please use Group By. See attached example.

thanks,

Rajesh Vaswani

Re: Max Date

jagan — Tue, 27 Aug 2013 03:32:21 GMT

Hi,

Try this script

Data:

LOAD

//ItemID,

Date

(Max(ReportDate)) AS MaxDate

//QTY

FROM

Date_Iss.xlsx

(

ooxml, embedded labels, table is Sheet1);

Regards,

Jagan.

Re: Max Date

Tue, 27 Aug 2013 03:44:33 GMT

Hi Jagan,

I need item ID as i have to to do left join with another table

Re: Max Date

jagan — Tue, 27 Aug 2013 03:51:06 GMT

Hi,

Try this

Data:
LOAD
ItemID,
Date(ReportDate, 'M/D/YYYY h:mm TT') AS ReportDate,
QTY
FROM
Date_Iss.xlsx
(ooxml, embedded labels, table is Sheet1);

INNER JOIN (Data)
LOAD
Date(Max(ReportDate), 'M/D/YYYY h:mm TT') AS ReportDate
//QTY
FROM
Date_Iss.xlsx
(ooxml, embedded labels, table is Sheet1);

Regards,

Jagan.

Re: Max Date

Tue, 27 Aug 2013 03:58:07 GMT

Hi Srinu,

Just copy paste this code:)think itshould work fine:)

Temp:

LOAD ReportDate as MaxDate
FROM
Date_Iss.xlsx
(ooxml, embedded labels, table is Sheet1);
LET vMaxDate =

peek('MaxDate');

Drop

Table Temp;

TableName:

LOAD

ItemID,

ReportDate,

QTY

FROM
Date_Iss.xlsx
(

ooxml,

embedded

labels,

table

is Sheet1)

WHERE

ReportDate = '$(vMaxDate)';

Re: Re: Max Date

Tue, 27 Aug 2013 04:14:37 GMT

Hi Jagan,

Please see new attachments. How my result should look like is in excel spreadsheet with sheet name "result"

Thanks

Re: Max Date

Tue, 27 Aug 2013 04:19:40 GMT

Hi Srinu,

After seeing your excel,i changed the code a bit.This should work for you.Hope this helps

Temp:

LOAD ReportDate as MaxDate
FROM Date_Iss.xlsx
(ooxml, embedded labels, table is Sheet1);
LET vMaxDate =peek('MaxDate');

Drop Table Temp;

TableName:
LOAD ItemID,

IF(ReportDate='$(vMaxDate)',ReportDate) AS ReportDate,

QTY
FROMDate_Iss.xlsx
(ooxml, embedded labels, table is Sheet1);

Re: Max Date

jagan — Tue, 27 Aug 2013 04:39:43 GMT

Hi,

Try like this

Data:

LOAD

ItemID

QTY

FROM

Test3.xlsx

(

ooxml, embedded labels, table is Main);

MaxDate:

LOAD

Date

(Max(ReportDate), 'M/D/YYYY h:mm TT') AS ReportDate

//QTY

FROM

Test3.xlsx

(

ooxml, embedded labels, table is Sheet1);

Inner

Join

(MaxDate)

LOAD

Date

(ReportDate, 'M/D/YYYY h:mm TT') AS ReportDate,

ItemID

FROM

Test3.xlsx

(

ooxml, embedded labels, table is Sheet1);

LEFT

JOIN

(Data)

LOAD

RESIDENT

MaxDate;

DROP

TABLE

MaxDate;

REgards,

Jagan.