0 Replies Latest reply: Dec 13, 2017 9:29 AM by Preamkumar R RSS

    Custom SQL in Qliksense

    Preamkumar R

      Hi ,

       

      I have connected to the hive and enabled i.e i have chosen the tables which i want to use. And i am trying to write an custom sql in load data editor but its throwing error like table not found.

       

      Can anyone help me out?

       

      Query which  i used is as below.

       

      SET ThousandSep=',';

      SET DecimalSep='.';

      SET MoneyThousandSep=',';

      SET MoneyDecimalSep='.';

      SET MoneyFormat='$#,##0.00;-$#,##0.00';

      SET TimeFormat='h:mm:ss TT';

      SET DateFormat='M/D/YYYY';

      SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

      SET FirstWeekDay=6;

      SET BrokenWeeks=1;

      SET ReferenceDay=0;

      SET FirstMonthOfYear=1;

      SET CollationLocale='en-US';

      SET CreateSearchIndexOnReload=1;

      SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

      SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';

      SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

      SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

       

       

      LIB CONNECT TO 'Apache_Hive_**.**.*.***';

       

       

      SELECT BOEPI.BILL_SIZE,

      DECODE(EPI.patient_type, '1', 'IP', '2', 'OP', '3', 'DC', '4', 'ED', EPI.patient_type ) PatientType,

      EPI.ADMIT_STATUS,EPI.ADMISSION_DATETIME,EPI.DISCHARGE_DATETIME,NAT.code_description AS NATIONALITY

      FROM FSCGKK.FDEPISODE EPI

      INNER JOIN FSCGKK.BOEPISODEACCOUNT BOEPI ON EPI.episode_no=BOEPI.episode_no

      INNER JOIN FSCGKK.FDPATIENT PAT ON EPI.fdpatient_id=PAT.fdpatient_id

      INNER JOIN FSCGKK.FDPERSON PER ON PAT.fdperson_id=PER.fdperson_id

      LEFT JOIN FSCGKK.VW_NAT NAT ON PER.nationality=NAT.code

      WHERE NAT.code_description NOT LIKE '%MALAYSIA%'

      AND EPI.ADMIT_STATUS IN ('4','IC');

       

       

       

       

      Error:

      The following error occurred:

      Connector reply error: Unable to get column information for the fields that are used in the query: ERROR [42S02] [Qlik][ODBC Apache Hive Wire Protocol driver][Apache Hive]Error while compiling statement: FAILED: SemanticException [Error 10001]: Line 6:11 Table not found 'FDPATIENT'