QlikView App Dev

[rebelfox]

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?

[t_chetirbok]

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]

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]

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]

maybe you use qualify?

[rebelfox]

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]

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]

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