How to add empty column while concatenating two tables?

Fri, 02 May 2014 08:16:13 GMT

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;

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

Anonymous — Fri, 02 May 2014 18:10:50 GMT

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.

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

anbu1984 — Sat, 03 May 2014 11:06:06 GMT

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

null as DistributionType,

topic How to add empty column while concatenating two tables? in QlikView

How to add empty column while concatenating two tables?

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

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