Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
zadravecm
Contributor III
Contributor III

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
Anil_Babu_Samineni

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

View solution in original post

1 Reply
Anil_Babu_Samineni

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)