Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
durgabhavani
Creator III
Creator III

Help me to find the lowest date?

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

1 Solution

Accepted Solutions
sunny_talwar

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:

Capture.PNG

View solution in original post

3 Replies
swuehl
MVP
MVP

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

sunny_talwar

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:

Capture.PNG

durgabhavani
Creator III
Creator III
Author

Sunny and Sweuhl, both are working. Thanks for response.