Expression to calculate

[#1]

Hello Everybody

I load 2 file

The first one

Load

Total

from file 1 .xls

The second one

Load Quart from 2.xls

How to calculate Ecart =  ( quart - Total / 13) knowing that quart and total come from a different file 1.xls and 2.xls

Thank U

[MK_QSL]

There should be a common field between these two tables...

Where is that?

[Anonymous]

simplest way is to have at least one field (unique key) that is common for QV to automatically associate the data so you can do your calculations.

there are other ways using $ sign expansions that you could achieve this but i prefer to keep it simple.

so something like

firsttable:

load Total, UniqueKey from [file 1.xls]

secondtable:

load Quart, UniqueKey from [file 2.xls]

if the unique keys have different names in first table and second table, you can rename one of the columns in your load statement by replacing UniqueKey in one of the tables with "Key as UniqueKey".

[#4]

the common file is Num SS

how can i do it please ??

[its_anandrjs]

If there is common field between them then you are able to join this tables and make single table

TableD:

Load CommanKeyField,

Total from file 1 .xls

Load CommanKeyField,

Quart from 2.xls

Then You can do

Load

CommanField

(( quart - Total ) / 13) as Ecart

Resident TableD;

Drop table TableD;

[#6]

Annand ,

QV indacate me that quart is not found in my table ???

its strange

[its_anandrjs]

Oh sorry use your quart field please find in your table fields

[#8]

Exactly, I have set the right fields

[its_anandrjs]

Provide me your load script please copy paste here.

[#10]

//************************************ Chargement du Fichier de Versemennt PEE ***************************************//
Versement :


LOAD 


  Matricule as VPEE_Matricule,
     [Nouvelle Numerotation] as Pivot_NouvelleNumerotation, 


     Nom as VPEE_Nom, 
     Prénom as VPEE_Prénom, 
     interessement AS VPEE_Interessement, 
     participation as VPEE_Patricipation, 
     exceptionnel as VPEE_Exceptionnel,

     P01 as VPEE_Montant1, 
     F01 as VPEE_Fond1,
  P02 as VPEE_Montant2, 
     F02 as VPEE_Fond2,



     P03 as VPEE_Montant3, 
     F03 as VPEE_Fond3, 
     P04 as VPEE_Montant4, 
     F04 as VPEE_Fond4, 
     P05 as VPEE_Montant5, 
     F05 as VPEE_Fond5, 
     P06 as VPEE_Montant6, 
     F06 as VPEE_Fond6, 
     P07 as VPEE_Montant7, 
     F07 as VPEE_Fond7, 
     P08 as VPEE_Montant8, 
     F08 as VPEE_Fond8, 
     P09 as VPEE_Montant9, 
     F09 as VPEE_Fond9, 
     P10 as VPEE_Montant10, 
     F10 as VPEE_Fond10, 
     P11 as VPEE_Montant11, 
     F11 as VPEE_Fond11, 
     P12 as VPEE_Montant12, 
     F12 as VPEE_Fond12,
     P01+P02+P03 as VPEE_TotaleGeneral 

     //+P04+P05+P06+P07+P08+P08+P10+P11+P12

FROM

(biff, embedded labels, table is [export site$]);






//**************************************************** Chargement du salaire **************************** //


Requette :


LOAD

  monthname ( '2014-02-1') as date  ,
  Matr. as VPEE_Matricule, 
     Montant as Fichier_Salaire_Montant, 

     [Salaire Base Th] as [Fichier_Salaire_Salaire Base Th],
Quart , //     Quart as Fichier_Salaire_Quart,
      Commune as lolo,

     [Etabl. (SIRET)] as Fichier_Salaire_Etablissement 

FROM

(biff, embedded labels, table is Feuil1$);




//Concatenate
//
//LOAD 
//
// monthname ( '2014-03-1') as date  ,
// Matr. as VPEE_Matricule, 
//
//     Montant as Fichier_Salaire_Montant, 
//     [Salaire Base Th] as [Fichier_Salaire_Salaire Base Th], 
//     Quart as Fichier_Salaire_Quart,
//   
//     [Etabl. (SIRET)] as Fichier_Salaire_Etablissement 
//     
//FROM
//
//(biff, embedded labels, table is Feuil1$);




LOAD  VPEE_Matricule ,
   ( Quart - VPEE_TotaleGeneral ) as Ecart

   Resident Versement ; 
   DROP Table Versement