Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

zadravecm
New Contributor II

Get min value in distinct load

Hi,

I have a table with data like this:

idtime_stampother_time_stamp
12017-09-11 12:302017-09-11 12:00
12017-09-11 12:302017-09-11 12:00
12017-09-11 12:302017-09-11 12:00
12017-09-11 12:302017-09-11 12:20
12017-09-11 12:302017-09-11 12:20
22017-09-11 13:302017-09-11 13:10
22017-09-11 13:302017-09-11 13:10
22017-09-11 13:302017-09-11 13:10
22017-09-11 13:302017-09-11 13:15
22017-09-11 13:302017-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:

idtime_stampother_time_stamp
12017-09-11 12:302017-09-11 12:00
12017-09-11 12:302017-09-11 12:20
22017-09-11 13:302017-09-11 13:10
22017-09-11 13:302017-09-11 13:15

Now I would like to get:

idtime_stampother_time_stamp
12017-09-11 12:302017-09-11 12:00
22017-09-11 13:302017-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.

1 Solution

Accepted Solutions

Re: Get min value in distinct load

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

1 Reply

Re: Get min value in distinct load

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

Community Browser