Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
DATE |
12/29/14 1:52:03 PM |
1/5/15 12:12:33 PM |
1/5/15 12:10:33 PM |
1/5/15 12:05:33 PM |
1/5/15 12:25:33 AM |
1/5/15 12:25:33 PM |
1/9/15 11:37:28 AM |
1/9/15 11:37:28 AM |
1/9/15 11:42:28 AM |
1/15/15 12:25:33 PM |
8/9/15 11:37:28 AM |
8/9/15 11:37:28 AM |
10/5/15 12:25:33 PM |
10/5/15 12:22:33 PM |
10/9/15 9:37:28 AM |
10/9/15 10:37:28 AM |
10/9/15 11:37:28 AM |
10/15/15 12:25:33 PM |
10/19/15 11:43:28 AM |
11/5/15 12:25:33 PM |
11/11/15 11:51:28 AM |
11/19/15 12:15:33 PM |
11/25/15 12:25:33 PM |
Now my scenario is to find the date having the minimum timestamp for respective dates. My output to be as follow.
DATE |
12/29/14 1:52:03 PM |
1/5/15 12:25:33 AM |
1/9/15 11:37:28 AM |
1/15/15 12:25:33 PM |
8/9/15 11:37:28 AM |
10/5/15 12:22:33 PM |
10/9/15 9:37:28 AM |
10/15/15 12:25:33 PM |
10/19/15 11:43:28 AM |
11/5/15 12:25:33 PM |
11/11/15 11:51:28 AM |
11/19/15 12:15:33 PM |
11/25/15 12:25:33 PM |
Please help me in writing expression.
Thanks,
Durga
Try this script:
Table:
LOAD DATE as TimeStamp,
Date(Floor(DATE)) as Date,
Time(Frac(DATE)) as Time;
LOAD * Inline [
DATE
12/29/14 1:52:03 PM
1/5/15 12:12:33 PM
1/5/15 12:10:33 PM
1/5/15 12:05:33 PM
1/5/15 12:25:33 AM
1/5/15 12:25:33 PM
1/9/15 11:37:28 AM
1/9/15 11:37:28 AM
1/9/15 11:42:28 AM
1/15/15 12:25:33 PM
8/9/15 11:37:28 AM
8/9/15 11:37:28 AM
10/5/15 12:25:33 PM
10/5/15 12:22:33 PM
10/9/15 9:37:28 AM
10/9/15 10:37:28 AM
10/9/15 11:37:28 AM
10/15/15 12:25:33 PM
10/19/15 11:43:28 AM
11/5/15 12:25:33 PM
11/11/15 11:51:28 AM
11/19/15 12:15:33 PM
11/25/15 12:25:33 PM
];
Right Join(Table)
LOAD Date,
Min(Time) as Time
Resident Table
Group By Date;
Output:
SET TimestampFormat = 'M/D/YY h:mm:ss TT';
LOAD Timestamp(min(DATE)) as DATE INLINE [
DATE
12/29/14 1:52:03 PM
1/5/15 12:12:33 PM
1/5/15 12:10:33 PM
1/5/15 12:05:33 PM
1/5/15 12:25:33 AM
1/5/15 12:25:33 PM
1/9/15 11:37:28 AM
1/9/15 11:37:28 AM
1/9/15 11:42:28 AM
1/15/15 12:25:33 PM
8/9/15 11:37:28 AM
8/9/15 11:37:28 AM
10/5/15 12:25:33 PM
10/5/15 12:22:33 PM
10/9/15 9:37:28 AM
10/9/15 10:37:28 AM
10/9/15 11:37:28 AM
10/15/15 12:25:33 PM
10/19/15 11:43:28 AM
11/5/15 12:25:33 PM
11/11/15 11:51:28 AM
11/19/15 12:15:33 PM
11/25/15 12:25:33 PM
]
GROUP BY floor(DATE);
Try this script:
Table:
LOAD DATE as TimeStamp,
Date(Floor(DATE)) as Date,
Time(Frac(DATE)) as Time;
LOAD * Inline [
DATE
12/29/14 1:52:03 PM
1/5/15 12:12:33 PM
1/5/15 12:10:33 PM
1/5/15 12:05:33 PM
1/5/15 12:25:33 AM
1/5/15 12:25:33 PM
1/9/15 11:37:28 AM
1/9/15 11:37:28 AM
1/9/15 11:42:28 AM
1/15/15 12:25:33 PM
8/9/15 11:37:28 AM
8/9/15 11:37:28 AM
10/5/15 12:25:33 PM
10/5/15 12:22:33 PM
10/9/15 9:37:28 AM
10/9/15 10:37:28 AM
10/9/15 11:37:28 AM
10/15/15 12:25:33 PM
10/19/15 11:43:28 AM
11/5/15 12:25:33 PM
11/11/15 11:51:28 AM
11/19/15 12:15:33 PM
11/25/15 12:25:33 PM
];
Right Join(Table)
LOAD Date,
Min(Time) as Time
Resident Table
Group By Date;
Output:
Sunny and Sweuhl, both are working. Thanks for response.