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;

[Anil_Babu_Samineni]

Perhaps this?

[myTable]:
FIRST 12 LOAD data AS data1,
timestamp as datetime;
SQL SELECT  top 12 `data` as data,
`timestamp`,
FROM myDB.`myTable`
order by timestamp desc;

[wuming79]

Hi, I got the results as below. Was yours working on your side? 

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

Not sure if a sample will help. Attached is a sample of my data but it's in excel. The actual data is in mySQL.  

[Anil_Babu_Samineni]

When error come field not found. We must look the field names. Can you look that first?
Try, select query and check then show yhe image in tabular format

[wuming79]

Hi,

The following works after I remove the comma before "From myDB.'myTable'.

But if I need to manipulate timestamp to something else for example: 

timestamp((timestamp/86400 + 25569 + 8/24),'DD/MM/YYYY h:mm:ss[.fff] TT') as datetime2, how can I do an ORDER BY datetime2 which was in the loading process?

FIRST 12 LOAD data AS data1,
timestamp as datetime;

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

[Anil_Babu_Samineni]

Try this? Because, Preceding load concept won't allow Order By clause in Qlik.

[myTable]:

Load *;
SELECT `data` as data, `timestamp` FROM myDB.`myTable` order by timestamp desc;

Final:

Noconcatenate

First 12 Load data as data1, timestamp((timestamp/86400 + 25569 + 8/24),'DD/MM/YYYY h:mm:ss[.fff] TT') as datetime 

Resident MyTable;

Order by timestamp((timestamp/86400 + 25569 + 8/24),'DD/MM/YYYY h:mm:ss[.fff] TT');

Drop Table MyTable;

[wuming79]

Does that mean there is no way to avoid loading the entire data before selecting the last x rows of the table thus it will always eat up all the memory to process?

[Anil_Babu_Samineni]

I didn't mean that, because for first few records qlik need to understand which records we need. That is case, order by clause will help for qlik sources. So, I mean order by won't work using preceding concept. What is the statement, after my post? Is my previous script still not working?

[wuming79]

Hi,

I assume "SELECTdata' as data", should be "SELECT 'data' as data1"? 

Using the syntax below: I have the following error message:

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

[myTable]:

Load *;
SELECT `data` as data, `timestamp` FROM myDB.`myTable` order by timestamp desc;

Final:

Noconcatenate

First 12 Load data as data1, timestamp((timestamp/86400 + 25569 + 8/24),'DD/MM/YYYY h:mm:ss[.fff] TT') as datetime 

Resident MyTable;
Order by timestamp((timestamp/86400 + 25569 + 8/24),'DD/MM/YYYY h:mm:ss[.fff] TT');
Drop Table MyTable;

Also, my last 2 lines is always highlighted.  

[Anil_Babu_Samineni]

Please remove the highlighted one

[wuming79]

Hi,

I removed the colon after "MyTable", but I encounter  error output as below.

[myTable]:

Load *;
SELECT `data` as data, `timestamp` FROM myDB.`myTable` order by timestamp desc;

Final:

Noconcatenate

First 12 Load data as data1, timestamp((timestamp/86400 + 25569 + 8/24),'DD/MM/YYYY h:mm:ss[.fff] TT') as datetime 

Resident MyTable;
Order by timestamp((timestamp/86400 + 25569 + 8/24),'DD/MM/YYYY h:mm:ss[.fff] TT');
Drop Table MyTable; 

Error output:

The following error occurred:
Unexpected token: '(', expected one of: ',', 'asc', 'desc'
The error occurred here:
Final:
Noconcatenate
First 12 Load data AS data1, 
	timestamp((timestamp/86400 + 25569 + 8/24),'DD/MM/YYYY h:mm:ss[.fff] TT') as datetime

Resident MyTable
Order by timestamp>>>>>>(<<<<<<(timestamp/86400 + 25569 + 8/24),'DD/MM/YYYY h:mm:ss[.fff] TT')

After colon was removed, the order by syntax was highlighted red as follows.