Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Problems to make join into two tables

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.

Tags (3)
1 Solution

Accepted Solutions
joonasjaspi
New Contributor III

Re: Problems to make join into two tables

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.

5 Replies
sasikanth
Valued Contributor III

Re: Problems to make join into two tables

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' ;

Not applicable

Re: Problems to make join into two tables

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.

INVENTDIMIDPROJECTPURCHLINEPRICE_UNITQTY_ORDEREDCURRENCY
314517FB0B013722-333,5817EUR
321727O3-579-22FB0B013722-333,5817EUR

Thanks.

joonasjaspi
New Contributor III

Re: Problems to make join into two tables

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.

Not applicable

Re: Problems to make join into two tables

Perfect Joonas. It works.

Thanks a lot.

ravic906
Contributor III

Re: Problems to make join into two tables

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.

Community Browser