Max Date

[#1]

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

[rajeshvaswani77]

Hi Srinu,

Please use Group By. See attached example.

thanks,

Rajesh Vaswani

[jagan]

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.

[#4]

Hi Jagan,

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

[jagan]

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.

[#6]

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

[#7]

Hi Jagan,

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

Thanks

[#8]

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

[jagan · Accepted solution]

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.