Need help with qlikview syntax to converting SQL nested select

[#1]

Hi,

Can someone help me convert this sql statement to qlikview script? I have these 2 records in qvd but not able to find the appropriate qlikview syntax to retrieve the record with smaller SQ#. Example below.

SELECT * FROM TableABC WHERE ClientID = 51710 AND SQ#=(select MIN(SQ#) from TableABC where ClientID=51710)

TableABC

ClientID SQ# TeamID Date

51710 2 4 20-JAN-11

51710 3 12 20-JAN-11

[rbecher · Accepted solution]

Hi Tommy,

you can do this with lookup( 😞

TableABC:
LOAD * INLINE [
 ClientID,SQ#,TeamID,Date
 51710,2,4,20-JAN-11
 51710,3,12,20-JAN-11
];

LookupTable:
LOAD ClientID,
min(SQ#) as min_SQ#
Resident TableABC
Group By ClientID;

ResultTable:
NOCONCATENATE LOAD *
Resident TableABC
WHERE ClientID = 51710 AND SQ#=Lookup('min_SQ#', 'ClientID', ClientID, 'LookupTable');

Drop Table TableABC;

- Ralf

[llauses243]

Hi Ralf,

Very good solution, because do match in-memory

Only for find excellence this is my offer, with aggr about key con 2 or more parts & omitt hardcode.

Good luck, Luis.

[#4]

Hi guys,

Thanks so much! It worked perfectly.