topic Re: Problems to make join into two tables in QlikView

Problems to make join into two tables

Tue, 01 Jul 2014 08:16:08 GMT

Hi,

First I apologize for my english.

I have to join two tables and i don't know what join I have to use.

I need all registers of the first table and all registers of the second table that don't be in the first table. If there are repeated that are in both tables, I need only the register that is in the first table.

Attach my code:

PURCHS:

LOAD

TEXT(PURCHID) & '-' & TEXT(LINENUM) as PURCHLINE,

TEXT(INVENTDIMID) as INVENTDIMID,

TEXT(DIMENSION3_) as PROJECT,

NUM(PURCHPRICE) as PURCHPRICE,

TEXT(CURRENCYCODE) as CURRENCYCODE,

DATE(CONFIRMEDDLV) as CONFIRMEDDLV,

NUM(QTYORDERED) as QTYORDERED

FROM

[$(Ruta_QVD)\PURCHLINE.qvd] (qvd)

WHERE DATAAREAID = 'tase' ;

JOIN

LOAD

TEXT(PURCHID) & '-' & TEXT(LINENUM) as PURCHLINE,

TEXT(INVENTDIMID) as INVENTDIMID,

TEXT(DIMENSION3_) as PROJECT,

NUM(PURCHPRICE) as PURCHPRICE,

TEXT(CURRENCYCODE) as CURRENCYCODE,

DATE(CONFIRMEDDLV) as CONFIRMEDDLV,

NUM(QTYORDERED) as QTYORDERED

FROM

[$(Rute_QVD)\PURCHLINE_2003.qvd] (qvd)

WHERE DATAAREAID = 'aeo' ;

this code sometimes returns two registers with the same purchline, for example, it happens if the project or another attribute is different in both tables. I only would need the register of the first table. How can I do it?

Thanks in advance.

Re: Problems to make join into two tables

sasikanth — Tue, 01 Jul 2014 08:20:53 GMT

simply concatenate both the tables

LOAD

TEXT(PURCHID) & '-' & TEXT(LINENUM) as PURCHLINE,

TEXT(INVENTDIMID) as INVENTDIMID,

TEXT(DIMENSION3_) as PROJECT,

NUM(PURCHPRICE) as PURCHPRICE,

TEXT(CURRENCYCODE) as CURRENCYCODE,

DATE(CONFIRMEDDLV) as CONFIRMEDDLV,

NUM(QTYORDERED) as QTYORDERED

FROM

[$(Ruta_QVD)\PURCHLINE.qvd] (qvd)

WHERE DATAAREAID = 'tase' ;

concatineate

LOAD

TEXT(PURCHID) & '-' & TEXT(LINENUM) as PURCHLINE,

TEXT(INVENTDIMID) as INVENTDIMID,

TEXT(DIMENSION3_) as PROJECT,

NUM(PURCHPRICE) as PURCHPRICE,

TEXT(CURRENCYCODE) as CURRENCYCODE,

DATE(CONFIRMEDDLV) as CONFIRMEDDLV,

NUM(QTYORDERED) as QTYORDERED

FROM

[$(Rute_QVD)\PURCHLINE_2003.qvd] (qvd)

WHERE DATAAREAID = 'aeo' ;

Re: Problems to make join into two tables

Tue, 01 Jul 2014 08:35:13 GMT

Hi sasikanth,

concatenating both tables, for purchline='FB0B013722-3' returns two registers. The first, in which project is null, is in the second table and the second is in the first table. I would need the register that belongs to the first table that has filled the project field.

INVENTDIMID	PROJECT	PURCHLINE	PRICE_UNIT	QTY_ORDERED	CURRENCY
314517		FB0B013722-3	33,58	17	EUR
321727	O3-579-22	FB0B013722-3	33,58	17	EUR

Thanks.

Re: Problems to make join into two tables

Anonymous — Tue, 01 Jul 2014 08:45:28 GMT

You can use "where exists" to load records that have been previously loaded. So in second table add:

[$(Rute_QVD)\PURCHLINE_2003.qvd] (qvd)

WHERE DATAAREAID = 'aeo' and not exists(PURCHLINE, TEXT(PURCHID) & '-' & TEXT(LINENUM));

And you wont get duplicates.

Re: Problems to make join into two tables

Tue, 01 Jul 2014 08:52:23 GMT

Perfect Joonas. It works.

Thanks a lot.

Re: Problems to make join into two tables

Anonymous — Tue, 01 Jul 2014 08:54:04 GMT

Hi,

follow this pattern..You will get the required output

load * Inline
[
cname,sales
a,100
b,200
c,400
a,500
];
Concatenate
load * Inline
[
cname,sales
a,100
b,200
c,400
a,500
d,600
];

load Distinct *
Resident C;

HTH,

Ravi N.