Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
IMPORTANT: Upcoming LEF Database Maintenance, Oct. 3rd - SEE DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
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;

Highlighted
Creator
Creator

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;

Highlighted
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;

Highlighted
Specialist
Specialist

maybe you use qualify?

Highlighted
Creator
Creator

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.

Highlighted
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;

Highlighted
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