Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

durgabhavani
Contributor

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

Tags (1)
1 Solution

Accepted Solutions

Re: Help me to find the lowest 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:

Capture.PNG

3 Replies
MVP
MVP

Re: Help me to find the lowest date?

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

Re: Help me to find the lowest 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:

Capture.PNG

durgabhavani
Contributor

Re: Help me to find the lowest date?

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

Community Browser