topic Need help with qlikview syntax to converting SQL nested select in QlikView

Need help with qlikview syntax to converting SQL nested select

Mon, 28 Mar 2011 20:20:15 GMT

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

Need help with qlikview syntax to converting SQL nested select

rbecher — Mon, 28 Mar 2011 22:25:32 GMT

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

Need help with qlikview syntax to converting SQL nested select

llauses243 — Mon, 28 Mar 2011 23:16:09 GMT

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.

Need help with qlikview syntax to converting SQL nested select

Tue, 29 Mar 2011 17:23:11 GMT

Hi guys,

Thanks so much! It worked perfectly.