Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ;
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
Ben,
here you will find many important things about models:
Perfect Your QlikView Data Model
Regards
Darek
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;
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:
"
"
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
thank U Ferran