topic Re: JOIN 3 TABLES AND CREATING NEW CALCULATED FIELD in QlikView

JOIN 3 TABLES AND CREATING NEW CALCULATED FIELD

Fri, 07 Nov 2014 10:16:46 GMT

Hi Everyone,

I wish to join the ITEMS table with the DOCUMENTS table to create the Category field, but there is a test on the field, ART_CATEG (Table ARTICLES) and DOC_STYPE (DOCUMENTS of the table), but there is no key between 2 tables, it is therefore necessary to go through the LINES table that contains the key dOCUMENTS (DOC_NUMERO) and key ARTICLES (ART_CODE).

Attached the files of the tables

When I execute this code, there are two errors (in color) that appear:

I have another error telling me that a field is non-existent

PS: data source: SQL server

Table introuvable
Concatenate (tmp_Link_Table)
LOAD
IF(ART_CATEG,
          if(DOC_STYPE = 'F', 'Produits Finis',
          if(DOC_STYPE = 'S', 'Produits Semi-Finis',
          if(DOC_STYPE = 'M', 'Matières 1ères',
          if(DOC_STYPE = 'O', 'Main d''oeuvre',
          if(DOC_STYPE = 'T', 'Sous-traitance',
          if(DOC_STYPE = 'A', 'Autres'
          ))))))
    ) as Catégorie
Resident ARTICLES

Champ introuvable - <DOC_NUMERO>
tmp_Link_Table:

LOAD *, "DOC_NUMERO", "DOC_STYPE" Resident ARTICLES;

Champ introuvable - <ART_CATEG>
Concatenate (ARTICLES)

LOAD "DOC_STYPE",
     IF(ART_CATEG,
              if(DOC_STYPE = 'F', 'Produits Finis',
              if(DOC_STYPE = 'S', 'Produits Semi-Finis',
              if(DOC_STYPE = 'M', 'Matières 1ères',
              if(DOC_STYPE = 'O', 'Main d''oeuvre',
              if(DOC_STYPE = 'T', 'Sous-traitance',
              if(DOC_STYPE = 'A', 'Autres'
              ))))))
        ) as [Catégorie]
Resident DOCUMENTS

where is a problem?!!

Re: JOIN 3 TABLES AND CREATING NEW CALCULATED FIELD

Gysbert_Wassenaar — Fri, 07 Nov 2014 12:32:27 GMT

Since you didn't post the complete script I can only guess which part is executed before the other parts. You seem to use the statement Concatenate (tmp_Link_Table) before you create the table tmp_Link_Table. And it looks like you try to load two fields "DOC_NUMERO" and "DOC_STYPE" from the resident table ARTICLES that do no exist in that table.

Re: JOIN 3 TABLES AND CREATING NEW CALCULATED FIELD

anbu1984 — Fri, 07 Nov 2014 12:37:56 GMT

Concatenate will union rows.

If you need to join, then use Join like

Join (tmp_Link_Table) -- This is for full outer join

Left Join(tmp_Link_Table)

Inner Join(tmp_Link_Table)

Re: JOIN 3 TABLES AND CREATING NEW CALCULATED FIELD

Fri, 07 Nov 2014 14:17:39 GMT

Hi,

first of all, i would like to Thank you for you help,

For about my problem, in fact, i would like to create a fied (Catégorie ) but, in first i must make a test on the field ART_CATEG in (Table ARTICLES) and DOC_STYPE (documents of the table), but there is no key between the two tables (items -Documents), so it is necessary to pass through the table row that contains the key documents (DOC_NUMERO) and key articles (ART_CODE).

this table is LIGNES Table.
You can see the script in the attached file. (ARTICLES.sql)

But, I could not solve this problem

Thank for All

regards

Re: JOIN 3 TABLES AND CREATING NEW CALCULATED FIELD

almamy_diaby — Tue, 25 Nov 2014 17:26:05 GMT

see the attached file