Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
MVP & Luminary
MVP & Luminary

Re: Qliksense: Select Top x rows before loading data?

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
Highlighted
Partner
Partner

Re: Qliksense: Select Top x rows before loading data?

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

Re: Qliksense: Select Top x rows before loading data?

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?

Highlighted
MVP & Luminary
MVP & Luminary

Re: Qliksense: Select Top x rows before loading data?

Put the first 12 on your initial load

 

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

Re: Qliksense: Select Top x rows before loading data?

Hi,

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

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

 

Highlighted
Creator
Creator

Re: Qliksense: Select Top x rows before loading data?

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.

MVP & Luminary
MVP & Luminary

Re: Qliksense: Select Top x rows before loading data?

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

Highlighted
Creator
Creator

Re: Qliksense: Select Top x rows before loading data?

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;

 

Highlighted
MVP & Luminary
MVP & Luminary

Re: Qliksense: Select Top x rows before loading data?

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;
Highlighted
Creator
Creator

Re: Qliksense: Select Top x rows before loading data?

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.