topic Re: Get max timestamp from table in load script in App Development

Get max timestamp from table in load script

tmdongacardoso — Sat, 16 Nov 2024 19:51:56 GMT

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

Re: Get max timestamp from table in load script

sunny_talwar — Fri, 18 Oct 2019 11:04:11 GMT

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

Re: Get max timestamp from table in load script

martinpohl — Fri, 18 Oct 2019 11:05:47 GMT

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

Re: Get max timestamp from table in load script

tmdongacardoso — Fri, 18 Oct 2019 11:07:09 GMT

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

Re: Get max timestamp from table in load script

tmdongacardoso — Fri, 18 Oct 2019 11:10:16 GMT

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.

Re: Get max timestamp from table in load script

martinpohl — Fri, 18 Oct 2019 11:10:37 GMT

but, your let-statement won't work

Use

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

Regards

Re: Get max timestamp from table in load script

martinpohl — Fri, 18 Oct 2019 11:12:49 GMT

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

Re: Get max timestamp from table in load script

tmdongacardoso — Fri, 18 Oct 2019 12:30:33 GMT

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.