topic How to join more than 2 tables in QlikView

How to join more than 2 tables

Tue, 13 Sep 2011 12:50:07 GMT

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.

How to join more than 2 tables

SunilChauhan — Tue, 13 Sep 2011 12:58:57 GMT

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;

Re: How to join more than 2 tables

gandalfgray — Tue, 13 Sep 2011 13:03:26 GMT

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

Re: How to join more than 2 tables

Wed, 14 Sep 2011 07:30:36 GMT

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