Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Calcul de 2 champs dans tables différentes

Bonjour,

Je souhaite calculer un champ de la table TableOccupations1 avec un champ de la table TableOccupations2 mais je n'arrive pas à trouver le bon code, j'en suis arrivé là:

TableOccupations1:

SQL SELECT CODE as IntervenantTO,

    HOURCOST as CoutHeureTO,

    ID as IDTECHNICIANTO

FROM CS02."CSRE_TECHNICIAN";

TableOccupations2:

SQL SELECT "HOURTYPE_ID" as IDHOURTYPEIDTO,

    ID as IDTECHRATETO,

    RATEHOURTYPE as RATEHOURTYPETO,

    "TECHNICIAN_ID" as IDTECHNICIANTO,

    HOURCOST resident TableOccupations1,

    (HOURCOST*RATEHOURTYPE) as PointagTO

FROM CS02."CSRE_TECHRATE";

Quelqu'un peut m'aider SVP?

Merci,

12 Replies
Not applicable
Author

Bonjour,

Il me semble qu'il faudrait charger les deux tables séparemment avant de les travailler pour obtenir la table souhaitée.

Quelles champs voulez-vous obtenir au final?

Not applicable
Author

Je voudrais obtenir ce champ calculé (HOURCOST*RATEHOURTYPE) dont chacun est dans une table différente,

Merci,

Not applicable
Author

Est-ce que cela conviendrai? Je ne l'ai pas testé.

TableOccupations1:

SQL SELECT CODE as IntervenantTO,

    HOURCOST as CoutHeureTO,

    ID as IDTECHNICIANTO

FROM CS02."CSRE_TECHNICIAN";

Temp_TableOccupations2:

SQL SELECT "HOURTYPE_ID" as IDHOURTYPEIDTO,

    ID as IDTECHRATETO,

    RATEHOURTYPE as RATEHOURTYPETO,

    "TECHNICIAN_ID" as IDTECHNICIANTO,

FROM CS02."CSRE_TECHRATE";

TableOccupations2:

LOAD * Resident Temp_TableOccupations2;

join

LOAD * Resident TableOccupations1;

Table_Calculee:

LOAD

           IDTECHNICIANTO,

           IDHOURTYPEIDTO,

           IDTECHRATETO,

           CoutHeureTO,

           CoutHeureTO * RATEHOURTYPETO as PointagTO

Resident TableOccupations2;

adelmeire
Contributor II
Contributor II

Bonjour,

tu pourrais essayer cela:

Tmp_TableOccupations:

SQL SELECT HOURTYPE_ID as IDHOURTYPEIDTO,    

     ID as IDTECHRATETO,    

     RATEHOURTYPE as RATEHOURTYPETO,    

     TECHNICIAN_ID as IDTECHNICIANTO

FROM CS02."CSRE_TECHRATE";

JOIN (Tmp_TableOccupations)

SQL SELECT CODE as IntervenantTO,    

     HOURCOST,    

     ID as IDTECHNICIANTO

FROM CS02."CSRE_TECHNICIAN";

TableOccupations:

NOCONCATENATE

LOAD IDTECHNICIANTO,

     IDHOURTYPEIDTO,    

     IDTECHRATETO,    

     RATEHOURTYPETO,    

     HOURCOST,    

     (HOURCOST*RATEHOURTYPE) as PointagTO

RESIDENT Tmp_TableOccupations;

DROP TABLE Tmp_TableOccupations;

Not applicable
Author

Merci à tous les 2, ça marche au poil,

j'ai mis la dernière avec juste 1 modif (HOURCOST*RATEHOURTYPE) as PointagTO remplacé par (HOURCOST*RATEHOURTYPETO) as PointagTO

A+

yvesqlik
Partner - Contributor III
Partner - Contributor III

Bonjour,

pour pouvoir utiliser un champs du table 1 dans le tableau 2, il fraudra que les 2 tables aient un champs avec le même nom:

par exemple:

Tableau1:

A,

B,

D

FROM CS02."CSRE_TECHNICIAN";

//Tableau2 temporaire, avec le champs commun tu introduis le champs du tableau 1 dans le tableau2 ceci grâce à la fonction left join

TempTableau2:

A,

X,

Y

FROM CS02."CSRE_TECHRATE";

left join

load

A,

D

resident Tableau1

//Tableau 2 final

Tableau2:

non concatenate

load *,

D*Y as ChampUtilisé

resident TempTableau2;

drop TempTableau2;

Si tu as des questions, n'existent pas à les poser, j'espère que l'explication est clair

Not applicable
Author

Merci pour ces explications je crois avoir compris le mécanisme alors je me lance dans un calcul supplémentaire avec le champ d'une autre table par ceui déjà obtenu, et ça ne marche pas, donc voilà ce que j'ai écrit :

Tmp_TableOccupations:

SQL SELECT HOURTYPE_ID as IDHOURTYPEIDTO,   

     ID as IDTECHRATETO,   

     RATEHOURTYPE as RATEHOURTYPETO,   

     TECHNICIAN_ID as IDTECHNICIANTO

FROM CS02."CSRE_TECHRATE";

JOIN (Tmp_TableOccupations)

SQL SELECT CODE as IntervenantTO,   

     HOURCOST as HOURCOSTTO,   

     ID as IDTECHNICIANTO

FROM CS02."CSRE_TECHNICIAN";

TableOccupations:

LOAD IDTECHNICIANTO,

     IDHOURTYPEIDTO,   

     IDTECHRATETO,   

     RATEHOURTYPETO,   

     HOURCOSTTO,   

     (HOURCOSTTO*RATEHOURTYPETO) as PointageTO

RESIDENT Tmp_TableOccupations;

DROP TABLE Tmp_TableOccupations;

Tmp_TableCoutPointage:

SQL SELECT DURATION as TempsOccupTO,

          "TECHNICIAN_ID" as IDTECHNICIANTO

FROM CS02."CSWO_OCCUPATION";

JOIN (Tmp_TableCoutPointage)

LOAD IDTECHNICIANTO,

     PointageTO

RESIDENT TableOccupations;

TableCoutPointage:

LOAD IDTECHNICIANTO,

     PointageTO*TempsOccupTO

RESIDENT Tmp_TableCoutPointage;

DROP TABLE Tmp_TableCoutPointage;

Merci pour votre aide,

adelmeire
Contributor II
Contributor II

et voila!

Tmp_TableOccupations:

SQL SELECT HOURTYPE_ID as IDHOURTYPEIDTO,    

     ID as IDTECHRATETO,    

     RATEHOURTYPE as RATEHOURTYPETO,    

     TECHNICIAN_ID as IDTECHNICIANTO

FROM CS02."CSRE_TECHRATE";

JOIN (Tmp_TableOccupations)

SQL SELECT CODE as IntervenantTO,    

     HOURCOST,    

     ID as IDTECHNICIANTO

FROM CS02."CSRE_TECHNICIAN";

JOIN (Tmp_TableOccupations)

SQL SELECT DURATION as TempsOccupTO,

          "TECHNICIAN_ID" as IDTECHNICIANTO

FROM CS02."CSWO_OCCUPATION";

TableOccupations:

NOCONCATENATE

LOAD IDTECHNICIANTO,

//     IDHOURTYPEIDTO,    

//     IDTECHRATETO,    

//     RATEHOURTYPETO,    

//     HOURCOST,

//     PointageTO,  

     (HOURCOST*RATEHOURTYPETO*TempsOccupTO) as  Result

RESIDENT Tmp_TableOccupations;

DROP TABLE Tmp_TableOccupations;

Not applicable
Author

J'y ai presque cru mais j'ai ce message :

Champ introuvable - <TempsOccupTO>

TableOccupations:

NOCONCATENATE

LOAD IDTECHNICIANTO,

         

     (HOURCOST*RATEHOURTYPETO*TempsOccupTO) as  CoutPointage

RESIDENT Tmp_TableOccupations