7 Replies Latest reply: Apr 21, 2017 4:29 AM by Zied Ahmed RSS

    solution for script

    Zied Ahmed

      I would like to use the result of the table Flotte_plaque in the condition in red and use a condition like if(stat_flotte='ACTIF',1,0) as statut_actif,(something like

      if exist(plaque)...as statut INACTIF

       

       

      ////*************set the qvd folder path************

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

       

      // LOAD * FROM [C:\Users\administrateur.VECTORYS\Desktop\Zied\qvdtest\Qvdflotte\Flotte.qvd] (qvd) ;//source des QVDs historisés

      //Concatenate:

       

       

       

      Flotte:

      LOAD

       

       

          * WHERE  "date_flotte"= Today();

       

       

      LOAD Plate as Plaque_flotte,

      Today() as date_flotte,

      TrailerType as Materiel_flotte,

        FrDes as Description,

        ParkName as Parc_flotte,

        DateDebutBlocage,

        NoteBlocage,

        SATUT as stat_flotte;

       

       

       

      SELECT Plate,

      TrailerType,

        FrDes,

        ParkName,

        DateDebutBlocage,

        NoteBlocage,

        SATUT

      FROM Suivi_Echeances;

      // ////*********store QVD**************

       

       

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

       

       

      Flotte_plaque:

      Load

      Plaque_flotte Resident Flotte;

      Left Join (Flotte_plaque)

       

      outer join(Fact_Final)

      load Distinct Plaque

       

      resident Fact_Final Where NOT Exists(Plaque_flotte,Plaque);

       

      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;

          • Re: solution for script
            Rahul Pawar

            Hello Zied,

             

            Trust that you are doing well!

             

            I considered that table Flotte_plaque & Flotte1 shares Plaque_flotte common field (linkage between two). As a workaround I have used Mapping Load feature & ApplyMap function of QlikView (refer below given sample script):

             

            Flotte_plaque:
            LOAD Plaque_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;
            Left Join (Flotte_plaque)
            outer join(Fact_Final)
            LOAD Distinct Plaque 
            resident Fact_Final
            Where NOT Exists(Plaque_flotte,Plaque);
            
            
            //Mapping load to generate statut_actif
            Map_Flotte_plaque_statut_actif:
            MAPPING LOAD 
                 Plaque_flotte,
                 statut_actif
            Resident Flotte_plaque;
            
            
            //Mapping load to generate statut_endom
            Map_Flotte_plaque_statut_endom:
            MAPPING LOAD
                 Plaque_flotte,
                 statut_endom
            Resident Flotte_plaque;
            
            
            //Mapping load to generate statut_bloque
            Map_Flotte_plaque_statut_bloque:
            MAPPING LOAD
                 Plaque_flotte,
                 statut_bloque
            Resident Flotte_plaque;
            
            Flotte1:
            LOAD
               Plaque_flotte ,
               Materiel_flotte,
               date_flotte,
               //consume above defined mapping loads
               ApplyMap('Map_Flotte_plaque_statut_actif', Plaque_flotte, 0) AS statut_actif,
               ApplyMap('Map_Flotte_plaque_statut_endom', Plaque_flotte, 0) AS statut_endom,
               ApplyMap('Map_Flotte_plaque_statut_bloque', Plaque_flotte, 0) AS statut_bloque
            Resident Flotte;
            
            
            DROP TABLE Flotte;
            

             

             

            Hope this will be of help.

             

            Regards!

            Rahul

              • Re: solution for script
                Zied Ahmed

                Hello Rahul...how i can use the the result of this :

                Left Join (Flotte_plaque)

                outer join(Fact_Final)

                LOAD Distinct Plaque

                resident Fact_Final

                Where NOT Exists(Plaque_flotte,Plaque);

                I need condition like the stat_endom and stat_actif for the list of Plaque...

                  • Re: solution for script
                    Rahul Pawar

                    Hello Zied,

                     

                    Could you please elaborate more on this? Also share the application file. This will help us to understand the problem and provide you expected inputs.

                     

                    Regards!

                    Rahul

                      • Re: solution for script
                        Zied Ahmed

                        Hello Rahul,

                        My problem is :

                        I have 2 tables: first table have the field Plaque (have all Plaques)

                        second table have the field Plaque_Flotte who have just a part of Plaques...so i use where exist to have the no common plaques (the difference) after that i need to affect these plaques to a statut like the stat_actif and the other stat in the next table

                          • Re: solution for script
                            Rahul Pawar

                            Hello Zied,

                             

                            I understood, if you have two tables where you want to get a status field from one table into another table. You can achieve the same using Mapping Load functionality (refer above given examples).

                             

                            Please correct me if I misinterpreted (Also share the your application).

                             

                            Regards!

                            Rahul