How to add Date Criteria in Data Loader Editor SQL Script

Tue, 17 Oct 2017 04:38:41 GMT

Hi Everyone,

I am new to Qilk Sense and having challenge trying to create a SQL script that will only load records where it Effective Date <= SYDATE

The following is my script

LOAD `sls_tty_id`,

`bsn_ar_grp_id`,

`eff_ts`,

`xpry_ts`,

`sls_tty_xtnd_nm`,

`bsn_ar_grp_nm`,

`st_bag_cust_sgm_cd`,

`st_bag_cust_sgm_nm`,

`st_bag_sls_cvrg_sgm_cd`,

`st_bag_sls_cvrg_sgm_nm`;

SQL SELECT DISTINCT `sls_tty_id`,

`bsn_ar_grp_id`,

`eff_ts`,

`xpry_ts`,

`sls_tty_xtnd_nm`,

`bsn_ar_grp_nm`,

`st_bag_cust_sgm_cd`,

`st_bag_cust_sgm_nm`,

`st_bag_sls_cvrg_sgm_cd`,

`st_bag_sls_cvrg_sgm_nm`

FROM HIVE.`ea_shared`.`st_bag_cust_pfl_dmnsn_rw_ext`

WHERE (Date(`eff_ts`, 'M/D/YYYY h:mm:ss TT') <= Date(Today(2),'M/D/YYYY'));

But I am getting the following error that Today is a invalid function

Connector reply error: SQL##f - SqlState: S1000, ErrorCode: 80, ErrorMsg: [Hortonworks][Hardy] (80) Syntax or semantic analysis error thrown in server while executing query. Error message from server: Error while compiling statement: FAILED: SemanticException [Error 10011]: Line 12:53 Invalid function 'Today'

Even if I hardcode a date, I end up loading 0 records

WHERE (Date(`eff_ts`, 'M/D/YYYY h:mm:ss TT') <= Date('10/17/2017 12:00:00 AM','M/D/YYYY h:mm:ss TT'));

Would appreciate if the community can share your experience working with dates in your data loader scripts.

topic How to add Date Criteria in Data Loader Editor SQL Script in Connectivity & Data Prep

How to add Date Criteria in Data Loader Editor SQL Script