Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
Hi Srinu,
Please use Group By. See attached example.
thanks,
Rajesh Vaswani
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.
Hi Jagan,
I need item ID as i have to to do left join with another table
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.
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)';
Hi Jagan,
Please see new attachments. How my result should look like is in excel spreadsheet with sheet name "result"
Thanks
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);
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.