QLIK SENSE loading data by script

[KimFerguson]

I can't figure out how to write an SQL script in QLIK SENSE. In HUE, Hive SQL works correctly: Kohl's Feedback Survey

`select d.* from  
(
select d.num, max(d.d_timestamp) as `maxt` 
from dictionary as d 
group by d.num
) as t 
left join dictionary as d ON t.num = d.num and t.maxt = d.d_timestamp
order by d.dictionary_id`

I need to write the same script, but when uploading data to QLIK SENSE. I've tried this, but it doesn't work:

   LOAD *;
SQL SELECT *
FROM 
(
SELECT dictionary_last.num, max(dictionary_last.d_timestamp) as maxt
FROM HIVE.`final`.`dictionary` as dictionary_last
) as t
left join gbv_dictionary_last ON t.num = dictionary_last.num and t.maxt = gbv_dictionary_last.d_timestamp
order by dictionary_last.id;

catching an exception: Connector reply error: SQL##f - SqlState: S1000, ErrorCode: 80, ErrorMsg: [Microsoft][Hardy] (80) Syntax or semantic analysis error thrown in server while executing query. Error message from server: Error while compiling statement: FAILED: SemanticException [Error 10001]: Line 7:10 Table not found 'dictionary_last'

[carlos_molino]

Try something like this:

[QUERY]:
LOAD *;

SQL
SELECT
dictionary_last.num as num,
max(dictionary_last.d_timestamp) as maxt
FROM
HIVE.`final`.`dictionary` as dictionary_last
GROUP BY dictionary_last.num;
left join
SELECT
*,
gbv_dictionary_last.d_timestamp
FROM
gbv_dictionary_last as maxt
ORDER BY
id;

that is, first you bring the table with the calculation of the Max and then you perform the left join with the other table for the common fields.

[williejacobs]

Hi Kim

If you want to join he data with SQL script I would try it this way.

LOAD *;
SQL SELECT *
FROM
(
SELECT dictionary_last.num, max(dictionary_last.d_timestamp) as maxt
FROM HIVE.`final`.`dictionary` as dictionary_last
) as t
left join
(Select *
From gbv_dictionary_last
) as gbv
ON t.num = gbv.num and t.maxt = gbv.d_timestamp
order by gbv.id;

However, do not Load (*) from the select as this would give you an ambiguous column name error due to the num column being on both tables.

[Calum]

Hi Kim,
I think the main error is the reference to dictionary_last in both the order by clause and the join (dictionary_last.id and dictionary_last.num). dictionary_last is an alias used in your subquery created to select your max time stamp but the subquery is aliased as t.
In your join you're joining t.num = dictionary_last.num, think this should be t.num = gbv_dictionary_last.num. You've also dropped the group by clause when you've put your SQL into Qlik for some reason.
As a rule, if the SQL works on the host system it will work as a call from QLIK, but be careful to fully specify your tables - so include both the database they're in and the schema.
Hope that helps.