Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

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;

Tags (1)
2 Replies
mov
Esteemed Contributor III

Re: How to add empty column while concatenating two tables?

SQL doesn't understand this function, but QV does.  Use preceding load, and move this function to the QV load part:


LOAD

     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

;

SQL SELECT

    recipient, 

    dte_letter,

    letter_no,

    addr_1,

    addr_2,

    suburb,

    'Manual',

      'tw267'

FROM whics.tw267

WHERE dte_letter BETWEEN '01-01-2014' AND '27-04-2014';

Edit: fixed some mistypings.

anbu1984
Honored Contributor III

Re: How to add empty column while concatenating two tables?

Null function Null() is not available in Sql. Replace Null() by Null keyword

null as DistributionType,

Community Browser