Error with IL qvd

Mon, 14 Oct 2013 12:05:09 GMT

//
//
//
//


// TO DO : normaliser les noms des fichiers
// Exemple : 24 - SIGNALETIQUE GROUPE




// load the file and rename the field names
Signalitique:
LOAD @1 as [salarie_matricule], 
     @2 as [salarie_id_ss], 
     @3 as [salarie_ste],  
     @4 as [salarie_etablissement],  
     @5 as [salarie_localisation_etablissement ], 
     @6 as [salarie_nom], 
     @7 as [salarie_prenom], 
     @8 as [salarie_classification], 
     @9 as [salarie_college], // degree of classification ( ex : manager , ingénieur .. college 1 , technicien : collége 2 )
     @10 as [salarie_salaire_base_mensuel], 
     @11 as [salarie_statut_salarie], 
     @12 as [salarie_statut_paie], 
     @13 as [salarie_lot_paie ], 
     @14 as [salarie_section], 
     @15        as [salarie_dernier_embauche],            
     @16            as [salarie_date_derniere_entree], 
     @17            as [salarie_ancienneté_ramenee ], 
     @18            as [salarie_derniere_date_de_sorti], 
     @19            as [salarie_motif_de_sortie],   
     @20            as [salarie_motif_absence], 
     @21            as [salarie_date_effet], 
     @23            as [salarie_satut], 
     @24            as [salarie_numero_rue ], 
     @25            as [salarie_numero_rue_2], 
     @26            as [salarie_type_adresse], 
     @27            as [salarie_libelle_rue], 
     @28            as [salarie_complementaire_adresse], 
     @29            as [salarie_bureau_distributeur], 
     @30            as [salarie_code_postale], 
     @31            as [salarie_commune], 
     @32            as [salarie_paye], 
     @33            as [salarie_date_de_nasissance], 
     @34            as [salarie_lieu_de_naissance], 
     @35 as [salarie_pays_naissance ], 
     @36 as [salarie_direction], 
     @37   as [salarie_code_banque], 
     @38    as [salarie_code_agence ], 
     @39    as [salarie_numero_compte], 
     @40 as [salarie_cle_controle], // 2 lastes numbers of a bank compte
     @41 as [salarie_description_banque], 
     @42    as [salarie nom_titulaire_du_compte], 
     @43 as [salarie_type_contrat], 
     @44 as [salarie_profil paie], 
     @45 as [salarie_CD_remuneration], 
     @46 as [salarie_ETP_paye], /* This is the equivalent of the part-time, 
         if a person works 80% it will be a 0.8 FTE
       if it is part-time so it'll be equal to 0.5. 
       100% => 1.
     This helps us to calculate the quarter of the employee's remuneration.*/
     
     @47 as [salarie_email], 
     @48 as [salarie_CD empl], 
     @49 as [salarie_emploi]
FROM
[$(FilePath)\fichier As\Signalitique PS\24 - SIGNALETIQUE GROUPE*.xls]
(biff, no labels);




// Laod a 2nd File : this file is sent by the bank( SOCIETE GENERALE) and it contains all the information about  PEE
PEE:
LOAD @1 as [PEE code entreprise], 
     @2 as [PEE nom], 
     @3 as [PEE prenom],  
     @4 as [PEE num compte],  
     @5 as [PEE num INSEE], 
     @6 as [PEE matricule], 
     @7 as [PEE statut salarié],  //  outgoing or incomming
     @8 as [PEE date embauche], 
     @9 as [PEE date depart salarie], 
     @10 as [PEE email], 
     @11 as [PEE abonnement a E-courier], 
     @12 as [PEE flag NPAI],
     @13 as [PEE code NFI], 
     @14 as [PEE completement lieu], 
     @15        as [PEE completement adresse],            
     @16            as [PEE num et libellé voie], 
     @17            as [PEE lieu dit], 
     @18            as [PEE code postale], 
     @19            as [PEE ville],   
     @20            as [PEE code pays], 
     @21            as [PEE nb parts dispo], 
     @23            as [PEE PAG indispo 5], 
     @24            as [PEE nb parts dispo 7], 
     @25            as [PEE PEE 7 relais], 
     @26            as [PEE PEE 7 nb parts dispo], 
     @27            as [PEE PEE 7 nb parts indispo], 
     @28            as [PEE PEE 5 nb parts dispo], 
     @29            as [PEE PEE 5 nb parts indispo], 
     @30            as [PEE nb parts dispo 5], 
     @31            as [PEE PEE 5 relais], 
     @32            as [PEE arcancia 455 dispo], 
     @33            as [PEE arcancia 455 indispo], 
     @34            as [PEE arcancia 257 dispo], 
     @35 as [PEE arcancia 257 indispo], 
     @36 as [PEE arcancia 654 dispo], 
     @37   as [PEE arcancia 654 indipo], 
     @38    as [PEE arcancia 351 dispo], 
     @39    as [PEE arcancia 351 indispo], 
     @40 as [PEE PAG dispo], 
     @41 as [PEE PAG indispo 7], 
     @42    as [PEE ccb], 
     @43 as [PEE deblocage exceptionnel 2013 perm 7], 
     @44 as [PEE deblocage exceptionnel 2013 perm 5]


     
     FROM        


    [$(FilePath)\fichier As\SG PEE\09 - PEE 02-10-13*.xls]
    (biff, no labels); 

Signalitique_ref:


LOAD 
  @1 as [salarie_matricule], 
  @2 as [salarie_id_ss],
     @6 as [salarie_nom],
  @7 as [salarie_prenom]



FROM [$(FilePath)\fichier As\Signalitique PS\24 - SIGNALETIQUE GROUPE*.xls]
(biff, no labels);
   










let ModificationTime ;
Let ThisExecTime = ReloadTime(); 
Let ThisExecTime = Now( ); 


SQL SELECT salarie_id_ss,salarie_matricule ,salarie_nom, salarie_prenom  FROM Signalitique_ref


WHERE ModificationTime >= #$(LastExecTime)#
AND ModificationTime < #$(ThisExecTime)#;


Concatenate LOAD salarie_id_ss, salarie_nom, salarie_nom, salarie_matricule FROM $(FilePath)\Dev\Signalitique.qvd;


where not(Exists(salarie_id_ss));






Inner Join SQL SELECT salarie_id_ss FROM Signalitique_ref;


If ScriptErrorCount = 0 then 


STORE Signalitique_ref INTO $(FilePath)\Dev\Signalitique_ref.QVD;


Let LastExecTime = ThisExecTime; 


End If

Why i have a error i, modificationTime &&

ODBC connection failed

SQL SELECT salarie_id_ss,salarie_matricule ,salarie_nom, salarie_prenom FROM Signalitique_ref

WHERE ModificationTime >= ##

AND ModificationTime < #14/10/2013 14:04:41#

Thank U

Re: Error with IL qvd

Gysbert_Wassenaar — Mon, 14 Oct 2013 13:25:42 GMT

The first time you run this LastExecTime will not have a value. You can see that it doesn't: WHERE ModificationTime >= ##. You'll need to give it a value for the first run or you will get the error.

You will also need to make sure that the datetime format is what your database expects. Perhaps you need quotes around the datetime values, perhaps not. I don't know what database you're retrieving data from.

topic Error with IL qvd in QlikView

Error with IL qvd

Re: Error with IL qvd