Qliksense: Select Top x rows before loading data?

[wuming79]

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;

[agigliotti]

did you try using debug with 12 rows setting ?

[timpoismans]

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]

Put the first 12 on your initial load

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

[wuming79]

Hi,

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

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

[wuming79]

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 · Accepted solution]

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]

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]

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]

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.