topic Re: Concatanation Problems in QlikView

Concatanation Problems

Tue, 08 Apr 2014 08:40:35 GMT

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 ;

Re: Concatanation Problems

Tue, 08 Apr 2014 08:51:13 GMT

Data, data model or expressions may cause this kind of problem.

Your script looks a little strange for me

May you show screenshot your expressions and data model (Ctrl+T) or even better attach your application with sample data.

regards

Darek

Re: Concatanation Problems

rohit214 — Tue, 08 Apr 2014 09:07:32 GMT

hi,

check with your Fichier_Salaries_Quart since it comes from diffrent table and i think there is no link between aur concatenate table and Requette table.

thanks

rohit

Re: Re: Concatanation Problems

Tue, 08 Apr 2014 09:08:22 GMT

Please See the att

Re: Re: Concatanation Problems

Tue, 08 Apr 2014 09:10:25 GMT

The links between in VPEE_Matricule,

Please see this sample example

Re: Concatanation Problems

Ferran_Garcia_Pagans — Tue, 08 Apr 2014 09:20:39 GMT

Ben,

I'm not sure about what are you trying to do but the table chart shows what yo have in the tables (data model).

If you are trying to accomplish oe salary per month the link between tables should be VPEE_Matricule+Month

Ferran.

Re: Re: Re: Concatanation Problems

Tue, 08 Apr 2014 09:21:22 GMT

Ben,

in my opinion you should work a little to perfect your data model. Try with star or snowflake schema. Sorry, but your model looks rather strange. I dont know what data volume you expect in your application, because sometimes developer have to do some "strange" things with model to get better performance. But in your case it rather doesn't look like this case.

I found, that for one key you have 2 records in each table. So, it is not strange that your tablebox schows cartesian

regards

Darek

Re: Concatanation Problems

Tue, 08 Apr 2014 09:27:40 GMT

Ferran

Yes that what i m searching for

But where i soulld add VPEE_Matricule+Month ??

Thank u for your help

Re: Concatanation Problems

Ferran_Garcia_Pagans — Tue, 08 Apr 2014 09:51:58 GMT

Because, the table chart you have in your application creates all possible combination between table Raquette and table V2-1.

If you use VPEE_Matricule as link there is 2 possible salaries for a matricule. If you use VPEE_Matricule+Month as a link, there is only one possible salary per month and matricule.

Ferran.

Re: Concatanation Problems

Tue, 08 Apr 2014 10:01:05 GMT

Ferran

Thank u for your help , I indersteand now

But can you attached me the source code please

Because i dont have the same result as you

Re: Re: Concatanation Problems

Ferran_Garcia_Pagans — Tue, 08 Apr 2014 10:24:19 GMT

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

Re: Re: Concatanation Problems

Tue, 08 Apr 2014 10:30:54 GMT

Ben,

here you will find many important things about models:

Perfect Your QlikView Data Model

Regards

Darek

Re: Re: Re: Concatanation Problems

Tue, 08 Apr 2014 12:26:56 GMT

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;

Re: Re: Re: Concatanation Problems

Ferran_Garcia_Pagans — Tue, 08 Apr 2014 14:12:48 GMT

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:

V2:
Load
Matricule as VPEE_Matricule,
P01+P02+P03 as VPEE_TotaleGeneral,
monthname ( '2014-02-1') as Mois,
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

Re: Concatanation Problems

Wed, 09 Apr 2014 09:35:05 GMT

thank U Ferran