topic Re: Join & exclude in QlikView

Join & exclude

Thu, 27 Nov 2014 13:12:04 GMT

Hi Experts

I have 2 Tables. One Field contains a lot of data. therefore I would like to load only port of the data. Thus I joined the tables to exclude part of the data:

Text:

SQL SELECT

txtnr,

memofeld

FROM EASY.texte;

Notiz:

left Join (Text)

SQL SELECT

notnr,

art,

thema,

notiz_typ,

txtnr

FROM EASY.notiz where notiz_typ is = 258;

Problem is, "memofeld" is still loaded completely with 1 Mio datasets. I would only like to load entries where notiz_typ is = 258.

Anybody know a solution?

Thank you for your help!

Jan

Re: Join & exclude

PrashantSangle — Thu, 27 Nov 2014 13:17:52 GMT

Hi,

try like,

Text:

SQL SELECT

notnr,

art,

thema,

notiz_typ,

txtnr

FROM EASY.notiz where notiz_typ is = 258;

left join

SQL SELECT

txtnr,

memofeld

FROM EASY.texte;

Regards

Re: Join & exclude

Thu, 27 Nov 2014 13:24:43 GMT

Hm, that one does not work yet...

Re: Join & exclude

jagan — Thu, 27 Nov 2014 13:27:34 GMT

Hi,

Use inner join

Text:

SQL SELECT

txtnr,

memofeld

FROM EASY.texte;

Notiz:

Inner Join (Text)

SQL SELECT

notnr,

art,

thema,

notiz_typ,

txtnr

FROM EASY.notiz where notiz_typ is = 258;

Regards,

Jagan.

Re: Join & exclude

anbu1984 — Thu, 27 Nov 2014 13:30:00 GMT

You can do the join in Sql

Text:

SQL SELECT

te.txtnr,

te.memofeld,

nz.notnr,

nz.art,

nz.thema,

nz.notiz_typ

FROM EASY.texte te, EASY.notiz nz

Where te.txtnr = nz.txtnr and nz.notiz_typ is = 258;

Or Use Inner join

Text:

SQL SELECT

txtnr,

memofeld

FROM EASY.texte;

Notiz:

Inner Join (Text)

SQL SELECT

notnr,

art,

thema,

notiz_typ,

txtnr

FROM EASY.notiz where notiz_typ is = 258;

Re: Join & exclude

Thu, 27 Nov 2014 13:43:00 GMT

I like the inner join, but it does not work. I still load all datasets from memofeld...

Re: Join & exclude

PradeepReddy — Thu, 27 Nov 2014 14:07:47 GMT

try the bellow scripts..

Option-1:

Notiz:

SQL SELECT

notnr,

art,

thema,

notiz_typ,

txtnr

FROM EASY.notiz where notiz_typ = '258';

Inner Join (Notiz)

Text:

SQL SELECT

txtnr,

memofeld

FROM EASY.texte;

Re: Join & exclude

anbu1984 — Thu, 27 Nov 2014 14:12:22 GMT

Check if you have spaces in field txtnr. You can also try inner join in Sql

Re: Join & exclude

PrashantSangle — Fri, 28 Nov 2014 04:42:51 GMT

Hi,

Did is = work properly?

just try with =

Text:

SQL SELECT

notnr,

art,

thema,

notiz_typ,

txtnr

FROM EASY.notiz where notiz_typ = '258';

left join

SQL SELECT

txtnr,

memofeld

FROM EASY.texte;

Regards

Re: Join & exclude

Fri, 28 Nov 2014 04:51:09 GMT

Hi,

Try this as per below example...

SoDetails:

LOAD

VBELN AS SalesOrder,

POSNR AS SalesOrderItem,

GBSTA AS SalesOrderStatus,

KUNNR AS SalesOrderCustomer;

SQL SELECT VBUP~VBELN, VBUP~POSNR, VBUP~GBSTA, VBAK~KUNNR FROM

VBUP LEFT JOIN VBAK ON VBUP~VBELN = VBAK~VBELN

WHERE VBUP~GBSTA = 'A';