Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
HI
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' ;
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.
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.
Perfect Joonas. It works.
Thanks a lot.
Hi,
follow this pattern..You will get the required output
C:
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.