Concatanation Problems

[#1]

Hello Evrebody

Someone can help me please , ii think i have a problem with my concatenation problem

I have 2 x the same months

Please see the source code in the att

//************************************ 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 as Fichier_Salaire_Quart,


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




//******************************************* Calcul  de l'ecart *******************************************//




V2:


Load
Matricule as VPEE_Matricule,
P01+P02+P03 as VPEE_TotaleGeneral


FROM

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


join(V2)


load
  Matr. as VPEE_Matricule,
  monthname ( '2014-02-1') as Mois,
  Quart

  FROM



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




LOAD


  VPEE_Matricule,
  Mois,


( Quart - VPEE_TotaleGeneral ) as Ecart  
Resident V2 ;  
DROP Table V2;









V3:


Load
Matricule as VPEE_Matricule,

P01+P02+P03 as VPEE_TotaleGeneral


FROM

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




join(V3)
load
  Matr. as VPEE_Matricule,
  Quart ,
  monthname ( '2014-03-1') as Mois 

  FROM

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




LOAD


  VPEE_Matricule,
  Mois,


( Quart - VPEE_TotaleGeneral ) as Ecart  
Resident V3 ;  
  DROP Table V3  ;

[Ferran_Garcia_Pagans]

Ben,

I'm not sure if this will be useful for you. As Dariusz said, I think you have to work a little more in the data model. I've add month to your

'requette' table. The main problem is that the granularity level in the 2 tables are different. This will show you the problem but I'm not sure if it'll be a god solution for your problem. Let me know if I can help you.

Ferran

[#3]

Ben,

here you will find many important things about models:

Perfect Your QlikView Data Model

Regards

Darek

[#4]

Ferra,

I try to do as you but i have this error

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 as Fichier_Salaire_Quart,


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








//******************************************* Calcul  de l'ecart *******************************************//




V2:


Load 
 Matricule as VPEE_Matricule,
P01+P02+P03 as VPEE_TotaleGeneral,
monthname ( '2014-02-1') as Mois,
VPEE_Matricule&'-'&Mois as link 


FROM

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


join(V2)


load 
  Matr. as VPEE_Matricule,
  monthname ( '2014-02-1') as Mois,
  VPEE_Matricule&'-'&Mois as link,
  Quart 


  FROM



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




 LOAD


  VPEE_Matricule  ,
  VPEE_Matricule&'-'&Mois as link,
  Mois,


( Quart - VPEE_TotaleGeneral ) as Ecart   
 Resident V2 ;   
 DROP Table V2;

[Ferran_Garcia_Pagans]

Hi Ben,

The problem is that I didn't have your excel files and I create my own files to test it. The files are similar but no exact.

Try this:

"

1. V2: 
2.  
3. Load  
4. Matricule as VPEE_Matricule, 
5. P01+P02+P03 as VPEE_TotaleGeneral, 
6. monthname ( '2014-02-1') as Mois, 
7. Matricule &'-'&monthname ( '2014-02-1')as link 

"

You also need to create a 'link' field in Requette table.

The problem is that you don't have a month in this table. For this reason, I told you the granularity are different between the 2 tables.  To solve it I create my own moth this table. Note that the the 'link' field is the result of concatenate Matricule and Month.

Regards, Ferran

[#6]

thank U Ferran