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

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
rbecher
MVP
MVP

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

Astrato.io Head of R&D

View solution in original post

3 Replies
rbecher
MVP
MVP

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

Astrato.io Head of R&D
llauses243
Creator III
Creator III

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
Author

Hi guys,

Thanks so much! It worked perfectly.