2 Replies Latest reply: May 3, 2014 7:37 AM by anbu cheliyan RSS

    How to add empty column while concatenating two tables?

      Hi all,

       

      Let me introduce Myself, I am Srujan working as a test analyst in Newcastle. I started learning Qlikview as I was fascinated with the data visualization capabilities the tool had. I do not have any understanding of BI concepts given my area of expertise is QA. But I have a fair bit of idea on SQL. I am new to Qlikview & BI domain as well. I hope to learn by asking questions and also referring to previously answered questions and of course with the good support of the Qlik Community.

       

      Here I am trying to concatenate two tables Customer and Correspondence and the tables after that.

      Question:

      While trying to create the table 'Correspondence' in which I added a new column 'DistributionType' which would be null in this table I am getting the following error "Error msg: "SQL##f - SqlState: 37000, ErrorCode: 4294967095, ErrorMsg: [Informix][Informix ODBC Driver][Informix]A syntax error has occurred."

      If I comment out Line 22, the script moves forward and creates the table.

       

      Could someone please guide me in resolving this issue?

      Your help is much appreciated.

       

      My script looks like this:

       

       

      ODBC CONNECT TO Informix1;
      
      //Customer Table 
      Customer:
      SQL SELECT customer,
        fund_id
        sex,
        dte_on,
          p_given,
          birth_dte
      FROM whics.tw202;
      
      //Manual Correspondence Table
      Correspondence:
      SQL SELECT recipient as customer, 
          dte_letter as process_dte_tw267_Manual,
          letter_no as CommType,
          addr_1,
          addr_2,
          suburb,
          'Manual' as TriggerType,
           null() as DistributionType, 
          'tw267' as DataSource
      FROM whics.tw267
      WHERE dte_letter BETWEEN '01-01-2014' AND '27-04-2014';
      
      //Vegas Correspondence Table//
      Concatenate (Correspondence)
      SQL SELECT client,
          customer, // replace with unique customer key linking field
      //  process_dte as process_dte_tw289_Vegas,
          process_dte,
          corresp_code as CommType,
          contact_meth,
          fund_id as fund_id_tw289_Vegas,
          null() as TriggerType, 
          'Fuji' as DistributionType,
          'tw289' as DataSource
      FROM whics.tw289
      WHERE process_dte BETWEEN '01-01-2014' AND '27-04-2014';
      
      //Emails sent Table//
      Concatenate (Correspondence) 
      Load *;
      SQL SELECT process_dte,
          recipient as customer,
          //fund_id as fund_id_tw289e_Email,
          corresp_code as CommType,
          null() as TriggerType,
          null() as DistributionType,
          'tw289e' as DataSource
      FROM whics.tw289e
      WHERE process_dte BETWEEN '01-01-2014' AND '27-04-2014';
      
      
      //Letter Description Table//
      tw262_LetterDescription:
      SQL SELECT letter_desc,
          letter_no as CommType,
          fund_id as fund_id_tw262_LetterDescription
      FROM whics.tw262;