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: 
tmdongacardoso
Contributor II
Contributor II

Get max timestamp from table in load script

Hi all.

I'm trying to write my load script so that it loads only new entries from my BigQuery tables. To do this I want to declare a variable with the max timestamp of the Table I already have in Qlik and use it in a WHERE condition, but I'm having problems declaring such variable. Here's what I'm trying:

LOAD Max(timestamp) AS [MaxTimestamp] Resident table_main_test;
Let Max_date = Timestamp([MaxTimestamp]);

LIB CONNECT TO [Google_BigQuery_altran-vodafonept-cpe-qa];

[table_main_test]:

SELECT `timestamp`,
`cpe_cpeid`,
`cpe_metadata_accessid`,
`cpe_vendor_model_sw_version`,
`props_cpe_vendor`,
`props_cpe_model_name`,
`props_cpe_sw_version`,
`cpe_uptime`,
`cpe_reboots`,
`avg_cpu`,
`avg_ram`,
`max`
FROM KPI.`table_main_test`
WHERE `timestamp` > '$(Max_date)'
;

Concatenate LOAD * Resident table_main_test ;

It gives the following error: 

Table 'table_main_test' not found.
 
But the table exists, so I'm not sure what's going on
Labels (1)
7 Replies
sunny_talwar

Where in the script are you using table_main_test? All I see is a reference to table_main

image.png

martinpohl
Partner - Master
Partner - Master

Did you checked that the table is existing in table viewer?

maybe an automatic concatenate has concatenate datas to another table?

so script:

table_main_test:

nocontatenate load

Regards

tmdongacardoso
Contributor II
Contributor II
Author

Yeah I miss wrote it when I was posting the question, it's table_main_test.  The problem is the same though

tmdongacardoso
Contributor II
Contributor II
Author

Hm no, this is the only table I have in the app. The weird thing is, If I use the expression 

LOAD * RESIDENT table_main_test after the SQL query to get the table from BigQuery, the script recognizes the name table_main_test. 

martinpohl
Partner - Master
Partner - Master

but, your let-statement won't work

Use

let Max_date = peek('MaxTimestamp',0,');

Regards

martinpohl
Partner - Master
Partner - Master

Yes, that's a normal function of Qlik.

If you load datas and the table schema (all fields) exists the datas are concatenated on the existing datas.

So you double the datas in the original table.

Statement 

load * resident table won't never work.

You have to add a field or

noconcatenate load * resident table

tmdongacardoso
Contributor II
Contributor II
Author

I can concatenate the results of the query with my table by doing:

CONCATENATE LOAD * RESIDENT table_main_test.

However, before doing that I want to get the highest timestamp value in my current table, that's where I am having problems and using NoConcatenate does not help.