topic two or more table (create from left joins) union script howto ? in QlikView

two or more table (create from left joins) union script howto ?

Fri, 25 May 2012 07:49:59 GMT

Hi,

i'm tring to do an union with two or "n" tables created from a join instructions but i didn't create the correct scripting,

I need to do a data union tab_A+Tab_B without creating syntetic keys

Any suggenstions ?

Thanks


tab_A:
SQL SELECT DTREG,
    NUMCON+'-'+cast(PRGCONTIA AS CHAR(6)) AS KUTE,
    TIPCON,
    NUMSVO,
    IDENTI1
FROM winsmartptia.dbo.C01FCSVUOT WHERE DTREG>='01/01/2011';
left join
SQL SELECT NUMCON+'-'+cast(CPROWNUM AS CHAR(6)) AS KUTE,
    CATEG,
    ANNORID
FROM winsmartptia.dbo."C01M_DTTIA" WHERE ANNORID>=2011;
left join
SQL SELECT TIPCON,
    DESCON,
    CONTE,
    TIPMAT
FROM winsmartptia.dbo.C01FCTIPCOT;
left join
SQL SELECT CATEG,
    DOMEST
FROM winsmartptia.dbo.C01TIATAR;
tab_B:
SQL SELECT DTREG,
    NUMCON+'-'+cast(PRGCONTIA AS CHAR(6)) AS KUTE,
    TIPCON,
    NUMSVO,
    IDENTI1
FROM winsmartptia.dbo.C02FCSVUOT WHERE DTREG>='01/01/2011';
left join
SQL SELECT NUMCON+'-'+cast(CPROWNUM AS CHAR(6)) AS KUTE,
    CATEG,
    ANNORID
FROM winsmartptia.dbo."C02M_DTTIA" WHERE ANNORID>=2011;
left join
SQL SELECT TIPCON,
    DESCON,
    CONTE,
    TIPMAT
FROM winsmartptia.dbo.C02FCTIPCOT;
left join
SQL SELECT CATEG,
    DOMEST
FROM winsmartptia.dbo.C02TIATAR;

two or more table (create from left joins) union script howto ?

Fri, 25 May 2012 08:05:41 GMT

Hi,

Try something like.

Tab_A_B:

load *

Resident tab_a;

concatenate load *

resident tab_b;

drop tables tab_a;

drop tables tab_b;

two or more table (create from left joins) union script howto ?

Fri, 25 May 2012 08:21:48 GMT

Thanks but..... it's not working, the result is that I've no tables/fields in qlik memory and no data.......

two or more table (create from left joins) union script howto ?

Fri, 25 May 2012 09:28:33 GMT

Did you put the code below your own code?

If you did there should be a new table Tab_A_B.

Re: two or more table (create from left joins) union script howto ?

Fri, 25 May 2012 09:44:16 GMT

Yes... as below but it'isnt working

tab_A:

SQL SELECT DTREG,

NUMCON+'-'+cast(PRGCONTIA AS CHAR(6)) AS KUTE,

TIPCON,

NUMSVO,

IDENTI1

FROM winsmartptia.dbo.C01FCSVUOT WHERE DTREG>='01/01/2011';

left join

SQL SELECT NUMCON+'-'+cast(CPROWNUM AS CHAR(6)) AS KUTE,

CATEG,

ANNORID

FROM winsmartptia.dbo."C01M_DTTIA" WHERE ANNORID>=2011;

left join

SQL SELECT TIPCON,

DESCON,

CONTE,

TIPMAT

FROM winsmartptia.dbo.C01FCTIPCOT;

left join

SQL SELECT CATEG,

DOMEST

FROM winsmartptia.dbo.C01TIATAR;

tab_B:

SQL SELECT DTREG,

NUMCON+'-'+cast(PRGCONTIA AS CHAR(6)) AS KUTE,

TIPCON,

NUMSVO,

IDENTI1

FROM winsmartptia.dbo.C02FCSVUOT WHERE DTREG>='01/01/2011';

left join

SQL SELECT NUMCON+'-'+cast(CPROWNUM AS CHAR(6)) AS KUTE,

CATEG,

ANNORID

FROM winsmartptia.dbo."C02M_DTTIA" WHERE ANNORID>=2011;

left join

SQL SELECT TIPCON,

DESCON,

CONTE,

TIPMAT

FROM winsmartptia.dbo.C02FCTIPCOT;

left join

SQL SELECT CATEG,

DOMEST

FROM winsmartptia.dbo.C02TIATAR;

Tab_A_B:

load *

Resident tab_A;

concatenate load *

resident tab_B;

DROP Tables tab_A;

DROP Tables tab_B;