Get min value in distinct load

Anonymous — Thu, 09 Nov 2017 08:12:54 GMT

Hi,

I have a table with data like this:

id	time_stamp	other_time_stamp
1	2017-09-11 12:30	2017-09-11 12:00
1	2017-09-11 12:30	2017-09-11 12:00
1	2017-09-11 12:30	2017-09-11 12:00
1	2017-09-11 12:30	2017-09-11 12:20
1	2017-09-11 12:30	2017-09-11 12:20
2	2017-09-11 13:30	2017-09-11 13:10
2	2017-09-11 13:30	2017-09-11 13:10
2	2017-09-11 13:30	2017-09-11 13:10
2	2017-09-11 13:30	2017-09-11 13:15
2	2017-09-11 13:30	2017-09-11 13:15

If I make a load

[my_table]:

Load Distinct

id,

time_stamp,

other_time_stamp

resident my_old_table;

Drop Table my_old_table;

What I get is:

id	time_stamp	other_time_stamp
1	2017-09-11 12:30	2017-09-11 12:00
1	2017-09-11 12:30	2017-09-11 12:20
2	2017-09-11 13:30	2017-09-11 13:10
2	2017-09-11 13:30	2017-09-11 13:15

Now I would like to get:

id	time_stamp	other_time_stamp
1	2017-09-11 12:30	2017-09-11 12:00
2	2017-09-11 13:30	2017-09-11 13:10

So I try:

[my_table]:

Load Distinct

id,

time_stamp,

min(other_time_stamp)

resident my_old_table;

Drop Table my_old_table;

But I get error when loading...

The following error occurred:

Invalid expression

I even try some other solutions from forums, like:

[my_table]:

Load Distinct

id,

time_stamp,

other_time_stamp

resident my_old_table

where id<>peek(id) order by id, other_time_stamp;

Drop Table my_old_table;

But I also get same error.

Re: Get min value in distinct load

Anil_Babu_Samineni — Thu, 09 Nov 2017 08:18:36 GMT

Try this?

[my_table]:

Load Distinct

id,

time_stamp,

other_time_stamp

resident my_old_table;

Drop Table my_old_table;

Right Join (my_table)

Load id, min(other_time_stamp) as other_time_stamp Resident my_table Group By id;

Note - Please play with Date format of Timestamp

topic Re: Get min value in distinct load in QlikView

Get min value in distinct load

Re: Get min value in distinct load