Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

How to join more than 2 tables

Hi,

This is my problem.

I have a fact table named CMOUVEDT.

I have another table which contains some lookup data : CACCOUTB.

I want to extract each lookup in simple data tables:

This is the script :

CMOUVEDT :

LOAD       MNGSOCCD,
           MNGPOSCD,
           DCEXPRCD as PERIODE,
           DOCPERCD as MOIS,
           DOCEXECD as ANNEE,
           JRNCODCD as JOURNAL,
           DOCNUMCD as PIECE,
           DOCLINCD as LIGNE,
           DOCDATCD,
           JRNTYPCD as JOURNAL_TYPE,
           GENACCCD as COMPTE,
           CRLCCUCD - DELCCUCD as MONTANT,
           ANAACCCD,
           FLRREFCD,
           COMNUMCD,
           SALNBRCD,
           PURNBRCD,
           MVTDSCCD as DESCRIPTION,
           CREUSRCD,
           CREDATCD,
           UPTUSRCD,
           UPTDATCD
                ;
SQL SELECT MNGSOCCD,
           MNGPOSCD,
           DCEXPRCD,
           DOCPERCD,
           DOCEXECD,
           JRNCODCD,
           DOCNUMCD,
           DOCLINCD,
           DOCDATCD,
           JRNTYPCD,
           GENACCCD,
           DELCCUCD ,
           CRLCCUCD,
           ANAACCCD,
           FLRREFCD,
           COMNUMCD,
           SALNBRCD,
           PURNBRCD,
           MVTDSCCD,
           CREUSRCD,
           CREDATCD,
           UPTUSRCD,
           UPTDATCD
           from
           CMOUVEDT
where GENACCCD between 600000000 and 799999999 And MNGSOCCD = 1 AND MNGPOSCD in (1,2);;
;

//*
LU_FOURNISSEURS :

LOAD * ;
SQL select COMNUMCP as COMNUMCD
           ,COMALPCP as CODE_FOURNISSEUR,
            COMNA1CP as FOURNISSEUR
  from ECOMPYTB where MNGSOCCP = '001'

;;

LU_PRODUITS :
LOAD * ;
SQL select ACCCODAC as  ANAACCCD
//,ACCDS1AC
,ACCALPAC  as PRODUIT
    from CACCOUTB where ACCTYPAC = 'ANA' and MNGSOCAC = '001' and MNGPOSAC = '002'
;;   

LU_DOSSIERS :
LOAD * ;
SQL SELECT ACCCODAC as FLRREFCD
   ,ACCALPAC  as DOSSIER
    from CACCOUTB where ACCTYPAC = 'FLD' and MNGSOCAC = '001' and MNGPOSAC = '002'
;;   

LU_COMPTES :
LOAD *;
SQL select ACCCODAC as GENACCCD
          ,ACCDS1AC as COMPTE_LIB,
          ACCALPAC,
          LE8001AC as COMPTE_GR1,
          LE8002AC as COMPTE_GR2,
          LE8003AC as COMPTE_GR3,
          LE8004AC as COMPTE_GR4,
          LE8005AC as COMPTE_GR5
    from CACCOUTB where ACCTYPAC = 'GEN' and MNGSOCAC = '001' and MNGPOSAC = '000'
;;
LU_PROJETS :
LOAD * ;
SQL  select NBRPRJPD as PURNBRCD
      ,ACCALPPD as PROJET,DESCR1PD 
     from EPRDETDT where MNGSOCPD = '001'and MNGPOSPD = '002'
;;
lu_services :

SQL select CONNUMCN as SALNBRCD
     ,CONNA1CN as SERVICE
from ECONTATB where MNGSOCCN = '001' and MNGPOSCN = '002' 
;;


So, I would like create another table named COMPTA, that "left join" CMOUVEDT with lookup table.

compta :

load * resident CMOUVEDT;

left join LOAD * Resident LU_COMPTES ;

left join LOAD * Resident LU_DOSSIERS;

left join load * resident LU_PRODUITS;

left join load * resident LU_FOURNISSEURS;

left join load * resident LU_SERVICES;

left join load * resident LU_PROJETS;


But the table named Compta is not created.

Why creating this table ? Because a need scan it later : i.e : select * from compta where .....

What the good way to do this ?

Thanks in advance.

Jérôme.

1 Solution

Accepted Solutions
chauhans85
Esteemed Contributor

How to join more than 2 tables

compta :

load * resident CMOUVEDT;

left join(Tablenametowhichlef join) LOAD * Resident LU_COMPTES ;

left join(Tablenametowhichlef join)  LOAD * Resident LU_DOSSIERS;

left join(Tablenametowhichlef join)  load * resident LU_PRODUITS;

left join(Tablenametowhichlef join)  load * resident LU_FOURNISSEURS;

left join(Tablenametowhichlef join)  load * resident LU_SERVICES;

left join(Tablenametowhichlef join)  load * resident LU_PROJETS;

3 Replies
chauhans85
Esteemed Contributor

How to join more than 2 tables

compta :

load * resident CMOUVEDT;

left join(Tablenametowhichlef join) LOAD * Resident LU_COMPTES ;

left join(Tablenametowhichlef join)  LOAD * Resident LU_DOSSIERS;

left join(Tablenametowhichlef join)  load * resident LU_PRODUITS;

left join(Tablenametowhichlef join)  load * resident LU_FOURNISSEURS;

left join(Tablenametowhichlef join)  load * resident LU_SERVICES;

left join(Tablenametowhichlef join)  load * resident LU_PROJETS;

gandalfgray
Valued Contributor

Re: How to join more than 2 tables

Hi jvan

Compta is not created because it contains exactly the same fields as CMOUVEDT.

The default behaviour of QV is in that case to concatenate the data to the already existing table.

Use Noconcatenate Load like this:

Compta:

Noconcatenate Load *

Resident CMOUVEDT;

hth/gg

Not applicable

Re: How to join more than 2 tables

Thanks all.

so I resolved my problem like this :

COMPTA :
LOAD     
MNGSOCCD,
           MNGPOSCD,
           DCEXPRCD as PERIODE,
           DOCPERCD as MOIS,
           DOCEXECD as ANNEE,
           JRNCODCD as JOURNAL,
           DOCNUMCD as PIECE,
           DOCLINCD as LIGNE,
           DOCDATCD,
           JRNTYPCD as JOURNAL_TYPE,
             GENACCCD as COMPTE,
           CRLCCUCD - DELCCUCD as MONTANT,
           ANAACCCD,
           FLRREFCD,
             COMNUMCD,
           SALNBRCD,
           PURNBRCD,
           MVTDSCCD as DESCRIPTION,
           CREUSRCD,
           CREDATCD,
           UPTUSRCD,
           UPTDATCD
                ;
SQL SELECT MNGSOCCD,
           MNGPOSCD,
           DCEXPRCD,
           DOCPERCD,
           DOCEXECD,
           JRNCODCD,
           DOCNUMCD,
           DOCLINCD,
           DOCDATCD,
           JRNTYPCD,
           GENACCCD,
           DELCCUCD ,
           CRLCCUCD,
           ANAACCCD,
           FLRREFCD,
           COMNUMCD,
           SALNBRCD,
           PURNBRCD,
           MVTDSCCD,
           CREUSRCD,
           CREDATCD,
           UPTUSRCD,
           UPTDATCD
           from
           CMOUVEDT
where GENACCCD between 600000000 and 799999999 And MNGSOCCD = 1 AND MNGPOSCD in (1,2)
and DOCEXECD = 2011 and DOCPERCD = 06;;
;

;;

LEFT JOIN (COMPTA)
LOAD *;
SQL select ACCCODAC as COMPTE
          ,ACCDS1AC as COMPTE_LIB,
////          ACCALPAC,
          LE8001AC as COMPTE_GR1,
          LE8002AC as COMPTE_GR2,
          LE8003AC as COMPTE_GR3,
          LE8004AC as COMPTE_GR4,
          LE8005AC as COMPTE_GR5
    from CACCOUTB where ACCTYPAC = 'GEN' and MNGSOCAC = '001' and MNGPOSAC = '000'
;;
LEFT JOIN (COMPTA)
LOAD * ;
SQL select COMNUMCP as COMNUMCD
          ,COMALPCP as CODE_FOURNISSEUR,
            COMNA1CP as FOURNISSEUR
    from ECOMPYTB where MNGSOCCP = '001'
;;
LEFT JOIN (COMPTA)
//LU_PRODUITS :
LOAD * ;
SQL select ACCCODAC as  ANAACCCD
         ,ACCALPAC  as PRODUIT
    from CACCOUTB where ACCTYPAC = 'ANA' and MNGSOCAC = '001' and MNGPOSAC = '002'
;;   
LEFT Join (COMPTA)
//LU_DOSSIERS :
LOAD * ;
SQL SELECT ACCCODAC as FLRREFCD
   ,ACCALPAC  as DOSSIER
    from CACCOUTB where ACCTYPAC = 'FLD' and MNGSOCAC = '001' and MNGPOSAC = '002'
;;   
//
//
LEFT JOIN (COMPTA)
//LU_PROJETS :
LOAD * ;
SQL  select NBRPRJPD as PURNBRCD
      ,ACCALPPD as PROJET,DESCR1PD 
     from EPRDETDT where MNGSOCPD = '001'and MNGPOSPD = '002'
;;
LEFT JOIN (COMPTA)
//LU_SERVICES :
load *;
SQL select CONNUMCN as SALNBRCD
          ,CONNA1CN as SERVICE
from ECONTATB where MNGSOCCN = '001' and MNGPOSCN = '002' 
;;


Bye

Community Browser