Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
rebelfox
Creator
Creator

Union On SQL Statement On Server

I'm trying to UNION together two tables with different field names on the database server and feed into a QlikView LOAD statement.

The database is an AS400 which is always awkward.

[MyTable]:
LOAD
    MyKey;

SQL SELECT AKey As MyKey
    FROM Table1
    WHERE AYear >= 2013

UNION

    SELECT BKey As MyKey
    From Table2
    WHERE BYear >= 2013;

However I get an error stating 'Field not found - <MyKey>'.

Is it possible to do this?

7 Replies
t_chetirbok
Specialist
Specialist

Hi!

is it work??

[MyTable]:
LOAD
    MyKey;

SQL SELECT AKey As MyKey
    FROM Table1
    WHERE AYear >= 2013 ;

concatenate(MyTable)

LOAD

    MyKey;

SQL    SELECT BKey As MyKey
    From Table2
    WHERE BYear >= 2013;

rebelfox
Creator
Creator
Author

Not quite but it has pointed me in the right direction.  The statement doesn't like the renaming of the field with the SQL statement.  If I rename it in the LOAD statement it works.  See below.

[MyTable]:
LOAD
    AKey As MyKey;

SQL SELECT AKey

    FROM Table1
    WHERE AYear >= 2013 ;

concatenate(MyTable)

LOAD

   BKey As MyKey;

SQL    SELECT BKey

    From Table2
    WHERE BYear >= 2013;

PradeepReddy
Specialist II
Specialist II

seems to be there is no issues with your script, it should work..

As I tried the same thing in SQL Server, it is working fine with out any issues...

Temp_Data:

Load Temp1;

SQL SELECT DT_SR_KEY as Temp1

FROM DATE_DIM where CLNDR_YR=2014

UNION

SELECT FILING_SR_KEY as Temp1

FROM FILING_TYP_DIM;

t_chetirbok
Specialist
Specialist

maybe you use qualify?

rebelfox
Creator
Creator
Author

I think this is another weird thing from the AS400/Iseries.  I'm always finding odd issues like this where it works on other platforms.

maxgro
MVP
MVP

try this (no preceding load) and after the reload look at the table viewer if your MyKey field is in uppercase


MyTable:

SQL

SELECT AKey as MyKey
    FROM Table1
    WHERE AYear >= 2013

UNION

    SELECT BKey as MyKey
    From Table2
    WHERE BYear >= 2013;

dimitri_obrant
Creator
Creator

Hello Roy,

you cannot use concantenate, because this solution is not the same how sql union command.

your solution is ok and works on my server. Works your sql query on sql Server? Booth keys should have the same data type. If sql querey works, buth QlikView not, you can try it:

[MyTable]:
LOAD
    MyKey;

SQL

SELECT MyKey

FROM

    (

    SELECT AKey As MyKey
        FROM Table1
        WHERE AYear >= 2013

    UNION

    SELECT BKey
       From Table2
       WHERE BYear >= 2013

  ) a

;

It works for me too