0 Replies Latest reply: Oct 17, 2017 12:38 AM by Jia Yang Cho RSS

    How to add Date Criteria in Data Loader Editor SQL Script

    Jia Yang Cho

      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.