Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
wuming79
Creator
Creator

Qliksense: Select Top x rows before loading data?

 

Hi,

Is there a way to Select top 12 without selecting all fields in SQL, load it, and then do a final load like below? 

My purpose is to lessen the load on Qliksense by selecting less data from SQL before loading to Qliksense.

[MyTable]:
LOAD data AS data1, 
     timestamp as datetime;
       
SELECT `data`, 
	`timestamp`, 
FROM myDB.`myTable`;

Final:
NoConcatenate
First 12 Load * Resident MyTable order by data1 asc, datetime asc;

Drop Table MyTable;

 

Labels (1)
1 Solution

Accepted Solutions
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

You can move the order by and Top into the SQL query. 

 

 FIRST 12 LOAD data AS data1,
timestamp as datetime;

SELECT  top 12 `data`,
`timestamp`,
FROM myDB.`myTable`
order by timestamp desc;

View solution in original post

20 Replies
agigliotti
Partner - Champion
Partner - Champion

did you try using debug with 12 rows setting ?
timpoismans
Specialist
Specialist

Not entirely sure, don't have a db at hand to test, but try:

[MyTable]:
LOAD data AS data1, 
     timestamp as datetime;
       
SELECT `data`, 
	`timestamp`, 
FROM myDB.`myTable`
WHERE RowNo()<12;

Though I suppose the data isn't in the right order when loading from SQL?

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Put the first 12 on your initial load

 

FIRST 12 LOAD data AS data1, 
     timestamp as datetime;
       
SELECT `data`, 
	`timestamp`, 
FROM myDB.`myTable`;
wuming79
Creator
Creator
Author

Hi,

When I added "WHERE RowNo()<12;", it will say 

 
The following error occurred:
Field 'data' not found
---
The error occurred here:
?
 

 

wuming79
Creator
Creator
Author

Hi,

Actually, I was going to do a selection of last 12 rows by doing a select top 12 order by timestamp desc. so that I can avoid loading the huge data set before doing order by.

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

You can move the order by and Top into the SQL query. 

 

 FIRST 12 LOAD data AS data1,
timestamp as datetime;

SELECT  top 12 `data`,
`timestamp`,
FROM myDB.`myTable`
order by timestamp desc;

wuming79
Creator
Creator
Author

Hi,

Sorry for the late reply. 

I tried the following but I get the error message "Field 'data' not found". 

FIRST 12 LOAD data AS data1,
timestamp as datetime;

[myTable]:
SELECT  top 12 `data`,
`timestamp`,
FROM myDB.`myTable`
order by timestamp desc;

 

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

In the load statements, you will need to specify `data` as the fieldname as it is in the SQL statement. Or alias it in the SQL statement. 

 

FIRST 12 LOAD `data` AS data1,
timestamp as datetime;

[myTable]:
SELECT  top 12 `data`,
`timestamp`,
FROM myDB.`myTable`
order by timestamp desc;

 or...

FIRST 12 LOAD data AS data1,
timestamp as datetime;

[myTable]:
SELECT  top 12 `data` as data,
`timestamp`,
FROM myDB.`myTable`
order by timestamp desc;
wuming79
Creator
Creator
Author

Hi,

Sorry for coming back here late. Was fixing other stuff.  I tried the 2 recommendations but I still have errors from Qliksense. 

FIRST 12 LOAD `data` AS data1,
timestamp as datetime;

[myTable]:
SELECT  top 12 `data`,
`timestamp`,
FROM myDB.`myTable`
order by timestamp desc;

Results:  Field 'timestamp' not found.

or...

FIRST 12 LOAD data AS data1,
timestamp as datetime;

[myTable]:
SELECT  top 12 `data` as data,
`timestamp`,
FROM myDB.`myTable`
order by timestamp desc;

 Results: Field 'data' not found.