Script to join 3 tables in one

[Anonymous]

Hi

I have 3 tables :

QV_RET_EMPLOYES.QVD

Columns :

NUM_OVED

YEAR_COMMERCIAL

HEURES_VALIDEES,

QV_RET_OVED_VW.QVD

Columns :

NUM_OVED

GROUPE_OVED

COMMERICAL_NUM

QV_PAIES.QVD

NUM_OVED

YEAR_CONTACT

SALAIRE_BRUT_TOTAL

I want to write a script that concatenate the 3 tables in one table (the bases is QV_PAIES.QVD) and I want in final the columns :

NUM_OVED

YEAR_FAMILLE (YEAR_CONTACT = YEAR_COMMERCIAL)

SALAIRE_BRUT_TOTAL

GROUPE_OVED

COMMERCIAL_NUM

HEURES_VALIDEES

I have tried this but it doesn't work :

QV_RET_EMPLOYES :

LOAD

NUM_OVED,

YEAR_COMMERCIAL as YEAR_FAMILLE,

HEURES_VALIDEES

FROM

\\As01\qlikview\loader\QV_RET_EMPLOYES.QVD

(qvd);

Join

QV_RET_OVED_VW :

LOAD

NUM_OVED,

GROUPE_OVED,

COMMERCIAL_NUM

FROM

\\As01\qlikview\loader\QV_RET_OVED_VW.QVD

(qvd);

Join

QV_PAIES_OVED:

LOAD

NUM_OVED,

YEAR_CONTACT as YEAR_FAMILLE,

SALAIRE_BRUT_TOTAL

FROM \\As01\qlikview\loader\QV_PAIES.QVD

(qvd);

QV_PAIES_FINAL:

LOAD

     NUM_OVED,

   YEAR_FAMILLE,

          SALAIRE_BRUT_TOTAL,

           GROUPE_OVED,

          COMMERCIAL_NUM,

          HEURES_VALIDEES

Resident QV_RET_OVED_VW;

Drop Table QV_RET_OVED_VW

Drop Table QV_EMPLOYES

Thank you for your help

[narender123]

A:

NUM_OVED

YEAR_COMMERCIAL

HEURES_VALIDEES

concatenation(Columns)

QV_PAIES.QVD

NUM_OVED

YEAR_CONTACT

SALAIRE_BRUT_TOTAL

C:

QV_PAIES.QVD

NUM_OVED

YEAR_CONTACT

SALAIRE_BRUT_TOTAL 

after this

NEW_A:

NUM_OVED

if(YEAR_COMMERCIAL=YEAR_CONTACT) as  YEAR_FAMILLE

HEURES_VALIDEES

NUM_OVED

YEAR_CONTACT

SALAIRE_BRUT_TOTAL

YEAR_FAMILLE

resident A

concatenate(NEW_A)

C:

NUM_OVED

YEAR_CONTACT

SALAIRE_BRUT_TOTAL

[#3]

you will have to use a composite key.

QV_RET_EMPLOYES :

LOAD

NUM_OVED,

YEAR_COMMERCIAL as YEAR_FAMILLE,

HEURES_VALIDEES

FROM

\\As01\qlikview\loader\QV_RET_EMPLOYES.QVD

(qvd);

Join

QV_RET_OVED_VW :

LOAD

NUM_OVED,

GROUPE_OVED,

COMMERCIAL_NUM

FROM

\\As01\qlikview\loader\QV_RET_OVED_VW.QVD

(qvd);

Data:

Load

NUM_OVED,

YEAR_FAMILLE,

NUM_OVED & YEAR_FAMILLE as Key,

HEURES_VALIDEES,

GROUPE_OVED,

COMMERCIAL_NUM

resident QV_RET_EMPLOYES;

join

QV_PAIES_OVED:

LOAD

NUM_OVED & YEAR_CONTACT  as Key,

SALAIRE_BRUT_TOTAL

FROM \\As01\qlikview\loader\QV_PAIES.QVD

(qvd);

Drop Table QV_RET_EMPLOYES;

[#4]

Try this :

QV_PAIES_FINAL :

LOAD

  NUM_OVED,

  YEAR_COMMERCIAL as YEAR_FAMILLE,

  HEURES_VALIDEES

FROM [\\As01\qlikview\loader\QV_RET_EMPLOYES.QVD] (qvd);

LEFT JOIN (QV_PAIES_FINAL) // Join on NUM_OVED

LOAD

  NUM_OVED,

  GROUPE_OVED,

  COMMERCIAL_NUM

FROM [\\As01\qlikview\loader\QV_RET_OVED_VW.QVD] (qvd);

LEFT JOIN (QV_PAIES_FINAL)  // Join on NUM_OVED and YEAR_FAMILLE

LOAD

  NUM_OVED,

  YEAR_CONTACT as YEAR_FAMILLE,

  SALAIRE_BRUT_TOTAL

FROM \\As01\qlikview\loader\QV_PAIES.QVD (qvd);

[Anonymous · Accepted solution]

Hi,

Try this:

QV_PAIES_FINAL:

LOAD

NUM_OVED,

YEAR_COMMERCIAL as YEAR_FAMILLE,

HEURES_VALIDEES

FROM

\\As01\qlikview\loader\QV_RET_EMPLOYES.QVD

(qvd);

Join

(QV_PAIES_FINAL)

LOAD

NUM_OVED,

GROUPE_OVED,

COMMERCIAL_NUM

FROM

\\As01\qlikview\loader\QV_RET_OVED_VW.QVD

(qvd);

Join

(QV_PAIES_FINAL)

LOAD

NUM_OVED,

YEAR_CONTACT as YEAR_FAMILLE,

SALAIRE_BRUT_TOTAL

FROM \\As01\qlikview\loader\QV_PAIES.QVD

(qvd);

At the end you will have the expected table with the columns:

NUM_OVED,YEAR_FAMILLE,SALAIRE_BRUT_TOTAL,GROUPE_OVED,COMMERCIAL_NUM,HEURES_VALIDEES

[#6]

Hi ,

       Your Code is  correct just a small change you have to do .

While Droping the Tables dont drop the table  "QV_RET_EMPLOYES " ;

There is no need to use Drop Table here.

Please let me know if there is anything .