Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
cid cdate ctime phone
101 10-dec-2013 10.15 1
101 11-dec-2013 5.15 2
102 10-dec-2013 3.15 3
102 10-dec-2013 4.15 4
103 10-dec-2013 6.15 5
103 10-dec-2013 7.15 6
103 11-dec-2013 8.15 7
i need for max(date ) in that max(date) i need max( time ) for coustmer 101,102 ,103 in script level please help me out
Use>>
aggr(max(cdate),cid)
and
aggr(max(ctime),cid)
in Script >>
Temp:
Load * from table;
Temp 1:
Load cid,max(cdate),max(ctime)
resident Temp;
group by cid;
noconcatenate
Final:
Load * resident Temp;
left join
Load * resident Temp 1;
Drop Table Temp;
Drop Table Temp 1;
Regards
Nitin
Hi Karthik,
Try this script
Data:
LOAD *,
TimeStamp(TimeStamp#(cdate & ':' & ctime, 'DD-MMM-YYYY:H.mm')) AS DateTime
INLINE [
cid,cdate,ctime,phone,
101,10-dec-2013,10.15,1
101,11-dec-2013,5.15,2
102,10-dec-2013,3.15,3
102,10-dec-2013,4.15,4
103,10-dec-2013,6.15,5
103,10-dec-2013,7.15,6
103,11-dec-2013,8.15,7];
INNER JOIN
LOAD
cid,
TimeStamp(Max(DateTime)) AS DateTime
RESIDENT Data
GROUP BY cid;
Regards,
Jagan.
can u please show me clear i mean scrren output how do u did am not geting here
hi mohan it has vast amount of data so i cannnot take them alll to inline na
1. Firstly just load your full table in temp directory.
2. Then >
temp1: load cid, max(cdate),max(ctime) resident temp group by cid;
3 Now
Final:
Load * resident Temp;
left join
Load * Resident temp1;
4 Drop all other tables.
Hope I am able to explain my self
Hi Karthik,
Replace Inline with you load script or select query
Data:
LOAD *,
TimeStamp(TimeStamp#(cdate & ':' & ctime, 'DD-MMM-YYYY:H.mm')) AS DateTime
FROM Data.xls;
INNER JOIN
LOAD
cid,
TimeStamp(Max(DateTime)) AS DateTime
RESIDENT Data
GROUP BY cid;
Regards,
Jagan.
i nithin ,
i requred at max(date), max(time) i requred wich custmer id, phone no
the one wich u have said is showing me
helllo mohan i want
max(date),max(time) ,cid, phone no
o/p form
cid cdate ctime phone
101 11-dec-2013 11.15 0
102 11-dec-2013 5.25 10
103 11-dec-2013 3.35 20
helllo mohan i want
max(date),max(time) ,cid, phone no
o/p form
cid cdate ctime phone
101 11-dec-2013 11.15 0
102 11-dec-2013 5.25 10
103 11-dec-2013 3.35 20