topic Re: Script to join 3 tables in one in QlikView

Script to join 3 tables in one

Anonymous — Tue, 09 Apr 2013 11:56:40 GMT

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

Re: Script to join 3 tables in one

narender123 — Tue, 09 Apr 2013 13:24:25 GMT

NUM_OVED

YEAR_COMMERCIAL

HEURES_VALIDEES

concatenation(Columns)

QV_PAIES.QVD

NUM_OVED

YEAR_CONTACT

SALAIRE_BRUT_TOTAL

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)

NUM_OVED

YEAR_CONTACT

SALAIRE_BRUT_TOTAL

Re: Script to join 3 tables in one

Tue, 09 Apr 2013 13:27:01 GMT

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;

Re: Script to join 3 tables in one

Tue, 09 Apr 2013 13:35:03 GMT

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

Re: Script to join 3 tables in one

Anonymous — Tue, 09 Apr 2013 13:56:05 GMT

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

Re: Script to join 3 tables in one

Tue, 09 Apr 2013 16:29:11 GMT

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 .