6 Replies Latest reply: Apr 24, 2017 5:28 AM by Zied Ahmed RSS

    Problem QVD

    Zied Ahmed

      Hello,

      I have this code and i want to use QVD and concatenate data :

       

      SET vQvdPath=C:\Users\administrateur.VECTORYS\Desktop\Zied\qvdtest\New;

       

       

       

      Flotte:

       

       

       

       

         

         

      LOAD Plate as Plaque_flotte,

      Today() as date_flotte,

      TrailerType as Materiel_flotte,

       

        ParkName as Parc_flotte,

       

       

        SATUT as stat_flotte;

         

      //[CG_FLOTTE_Suivi_Echeances]:

      SELECT Plate,

      TrailerType,

       

        ParkName,

       

       

        SATUT

      FROM

       

       

       

       

      NoConcatenate

      Plaque_all:

      load Distinct Plaque,

      '1' as statut_inactif,

      Type_Materiel as Materiel_flotte,

      Today() as date_flotte

       

       

      resident Fact_Final Where NOT Exists(Plaque_flotte,Plaque) and Type_Materiel<>'DiversMateriel';

       

       

      Concatenate

      Flotte1:

       

       

      Load

         Plaque_flotte ,

         Materiel_flotte,

         date_flotte,

         

        if(stat_flotte='ACTIF',1,0) as statut_actif,

         

               

                     if(stat_flotte='INACTIF',1,0)  as statut_endom,

              if(stat_flotte='BLOQUE',1,0)  as statut_bloque

          Resident Flotte;

          drop table Flotte;

       

       

       

      NoConcatenate

          Flotte2:

        

          load

          //stat_flotte,

         Plaque,

               date_flotte,

               Materiel_flotte,

            Plaque_flotte as Plaque_flotte,

              sum(statut_endom) as statut_endom,

             

             // statut_active,

               sum(statut_actif)  as statut_actif,

             sum(statut_inactif)  as statut_inactif,

           

                                             //ici

              sum(statut_bloque)  as statut_bloque,

             (sum(statut_actif)/day(date_flotte)) as Taux

              resident Plaque_all

            

            Group by date_flotte,Materiel_flotte,Plaque_flotte,Plaque

            ;

           

             

            drop table Plaque_all;

        

       

       

         

       

       

       

      NoConcatenate

            

          Flotte3:

          load

       

          Plaque,

          statut_inactif,

          statut_bloque,

          statut_endom,

          date_flotte,

         Plaque_flotte as Plaque_flotte,

         Materiel_flotte,

       

         

          if (statut_actif>statut_bloque and statut_actif>statut_inactif,1,

          (if(statut_bloque>statut_actif and statut_bloque>statut_inactif,2,

          (IF(statut_inactif>statut_actif and statut_inactif>statut_bloque,3))))) as Statut_cond,

         

           Taux ,

                     

         statut_actif

       

       

        

          Resident Flotte2

        

          ;

          drop table Flotte2;

       

          

      NoConcatenate

          Flotte4:

        

          load

         Plaque,

       

         statut_actif,

          date_flotte,

          statut_inactif,

          statut_bloque,

          statut_endom,

          //Statut_sup,

               Plaque_flotte as Plaque_flotte,

               Materiel_flotte,

               Taux,

         

         

           if (Statut_cond=1,'Actif',

          (if (Statut_cond=2,'Bloquée',

          (IF(Statut_cond=3,'Inactif'))))) as Statut_sup

         

       

          Resident Flotte3;

          drop table Flotte3;

       

      //pour SR sans traversée

          NoConcatenate

          FlotteFinal:

        

       

          LOAD Plaque,

          

              date_flotte,

               Plaque_flotte as Plaque_flotte,

               Materiel_flotte FROM [C:/QVD_final.qvd] (qvd) ;//source des QVDs historisés

      Concatenate:

      LOAD

       

       

          * WHERE  "date_flotte"= Today();

          load

         //Plaque,

          date_flotte,

        

                Plaque_flotte as Plaque_flotte,

               Materiel_flotte,

               Taux,

               'ON'  as STATUT,

               count(Statut_sup) as nbr

               resident Flotte4

               where Statut_sup='Actif'

               group by

              date_flotte,

               Taux,

               //[%Date ID],

               Plaque_flotte,

               Materiel_flotte;

            

             

               Concatenate  

                 load

            Plaque,

           

              date_flotte,

               Plaque_flotte as Plaque_flotte,

               Materiel_flotte,

                 'OFF'  as STATUT,

               count(Statut_sup) as nbr

               resident Flotte4

               where Statut_sup='Inactif'

               group by  date_flotte,

               Plaque_flotte,

               Materiel_flotte,Plaque;

              

           

                Concatenate  

                 load

             date_flotte,

           

               Plaque_flotte as Plaque_flotte,

               Materiel_flotte,

                'Bloquée'  as STATUT,

               count(Statut_sup) as nbr

               resident Flotte4

               where Statut_sup='Bloquée'

               group by  date_flotte,

               Plaque_flotte,

               Materiel_flotte;

      drop table Flotte4;

                Concatenate

                   Load*

                   resident Fact_Final ;

                   drop table Fact_Final;

                 

       

       

      STORE [FlotteFinal] into [$(vQvdPath)\QVD_final.qvd.qvd](qvd);

       

      I would like to stoke data of the last table but the problem i have synthetic keys after the concatenate

      Regards