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: 
Not applicable

How to select a distinct row from resident table

Hello,

I have a resident table "TMP1" with:

codclient_name
initial_date
1John01/01/2011
1Paul12/12/2010
2Georgia05/06/2011
3Marie07/30/2011
2Jack02/30/2011

But, I need to have just one row for each "cod" depending on the max initial_date.

So, my new table should have:

1 - John - 01/01/2011

2 - Georgia - 05/06/2011

3 - Marie - 07/30/2011

With SQL I could do:

select cod, client_name, initial_date from tmp1 t1

where client_name = (select top 1 client_name from tmp1 t2 where t1.cod = t2.cod order by initial_date desc);

But I need to do this with my resident table and not accessing the database again.

Do somebody have any idea how to do that?

Thanks!!!

1 Solution

Accepted Solutions
Not applicable
Author

Hi,

See this code.

TMP1:
LOAD
Cod,
Client_Name,
Initial_Date,
Cod & '#' & Initial_Date AS KEY_TMP1;
LOAD
Cod,
Client_Name,
Initial_Date
INLINE [
    Cod, Client_Name, Initial_Date
    1, John, 01/01/2011
    1, Paul, 12/12/2010
    2, Georgia, 05/06/2011
    3, Marie, 07/30/2011
    2, Jack, 02/30/2011
];

// This table filter the max date for each code

SELECT_CLIENT:
LOAD
Cod & '#' & Max_Date AS KEY;
LOAD
Cod,
DATE(MAX(Initial_Date), 'MM/DD/YYYY') AS Max_Date
Resident TMP1
GROUP BY Cod;

// This table using the function "EXISTS" to return just one information for the table TMP1

RESULT:
LOAD
Cod,
Client_Name,
Initial_Date
RESIDENT TMP1
WHERE EXISTS(KEY, KEY_TMP1);

DROP TABLE SELECT_CLIENT;
DROP TABLE TMP1;

View solution in original post

2 Replies
Not applicable
Author

Hi,

See this code.

TMP1:
LOAD
Cod,
Client_Name,
Initial_Date,
Cod & '#' & Initial_Date AS KEY_TMP1;
LOAD
Cod,
Client_Name,
Initial_Date
INLINE [
    Cod, Client_Name, Initial_Date
    1, John, 01/01/2011
    1, Paul, 12/12/2010
    2, Georgia, 05/06/2011
    3, Marie, 07/30/2011
    2, Jack, 02/30/2011
];

// This table filter the max date for each code

SELECT_CLIENT:
LOAD
Cod & '#' & Max_Date AS KEY;
LOAD
Cod,
DATE(MAX(Initial_Date), 'MM/DD/YYYY') AS Max_Date
Resident TMP1
GROUP BY Cod;

// This table using the function "EXISTS" to return just one information for the table TMP1

RESULT:
LOAD
Cod,
Client_Name,
Initial_Date
RESIDENT TMP1
WHERE EXISTS(KEY, KEY_TMP1);

DROP TABLE SELECT_CLIENT;
DROP TABLE TMP1;

Not applicable
Author

Thanks Eduardo,

You got it!!|!

I will try in my application (it´s a little bit more complicated than the example above).

But I think it will work.