Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
Not applicable

Need help with qlikview syntax to converting SQL nested select

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



1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Need help with qlikview syntax to converting SQL nested select

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

View solution in original post

3 Replies
MVP & Luminary
MVP & Luminary

Need help with qlikview syntax to converting SQL nested select

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

View solution in original post

llauses243
Contributor III

Need help with qlikview syntax to converting SQL nested select

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.

Not applicable

Need help with qlikview syntax to converting SQL nested select

Hi guys,

Thanks so much! It worked perfectly.