19 Replies Latest reply: Mar 7, 2017 6:10 AM by Sébastien Fatoux RSS

    Help with script !

    omar bensalem

      Hi everyone ( stalwar1),

       

      I have an issue I couldn't explain in the title; here it is:

       

      I have a field called Statut flotte (0,1 or 2) and a Num_voyage(0.1.2.3....)

       

      I want to create a new field

      statut remorque:

      if statut flotte =0 , bloqué

      if statut flotte =2 , non active cassée

      if statut flotte =1 and num voyage=0 , non actif

      if statut flotte =1 and num voyage>0, actif

       

      And I want this new statut remorque to be by month :

      + I want to count the 'nombre_traversée' in the script;

       

       

       

       

      Here is what I've done:

      //MyTable:

      LOAD

          "%Date SEQ",

          "%Date ID",

          "Calendar date",

          "Day of date",

          "Week of date",

          "Month of date",

          "Quarter of date",

          "Year of date",

          "Year week date",

          "Year month date",

          "week date",

          "quarter date",

          "Year quarter date",

          "Week start date",

          "Week end date",

          "Month start date",

        ..................

      FROM [lib://qvd/D_Calendar_28-02-017.qvd]

      (qvd);

       

       

      LOAD

          TimeframeId,

          Timeframe

      FROM [lib://qvd/Timeframe_28-02-017.qvd]

      (qvd);

       

      final:

      LOAD

      month(Date_Dossier) as month_dossier,

          "%Date ID",

          Code_Activite,

          Designation_Activite,

          Sens,

          Filiale_Creation_Dossier,

          "Filiale Origine",

          "Pays Fililale Origine",

          "Filiale Destination",

          Code_Ligne,

          Num_Dossier,

          Num_Voyage,

          Date_Dossier,

          Date_Embarquement,

          Date_Debarquement,

          "Statut Flotte",

          Groupe_Complet_Vide,

          Plaque,

          Parc,

          Type_Materiel,

          Compagnie,

          Remorque_Parc_HorsParc,

          Voyage_Ligne,

          Code_Carrier,

          Pays,

          PAYS2,

          Charge_Vide

      FROM [lib://qvd/final_28-02-017.qvd]

      (qvd)

       

       

      Where Designation_Activite='RORO' and (Parc=('VECTORYS FRANCE') or Parc=('VECTORYS ITALIE'));

       

       

       

      What I've done :

       

      tmp1:

      Load *, if("Statut Remorque1"=-1, 'bloquée') as "Statut Remorque";

       

      load  (month_dossier),"Statut Flotte",Num_Voyage,Plaque,

      count(Num_Dossier) as Nombre_Traversée,

       

      sum(DISTINCT if(("Statut Flotte"=0),-1)) as "Statut Remorque1"

       

      Resident final

      Group by (month_dossier),"Statut Flotte",Num_Voyage,Plaque ;

       

      ////////////////////////////////////////////

      Concatenate

      Load *, if("Statut Remorque1"=2, 'Non active cassée') as "Statut Remorque";

      load  (month_dossier),"Statut Flotte",Num_Voyage,Plaque,

      count(Num_Dossier) as Nombre_Traversée,

       

      sum(DISTINCT if(("Statut Flotte"=2),2)) as "Statut Remorque1"

       

      Resident final

      Group by (month_dossier),"Statut Flotte",Num_Voyage,Plaque ;

      /////////////////////////////////////////////////

      // Concatenate

      // Load *, if("Statut Remorque1"=0, 'Non actif', 'Actif') as "Statut Remorque";

       

       

      // load  (month_dossier),"Statut Flotte",Num_Voyage,Plaque,

      // count(Num_Dossier) as Nombre_Traversée,

       

       

      // sum(DISTINCT if   (("Statut Flotte"=1 and Num_Voyage=0 ), 0,

      //  if   (("Statut Flotte"=1 and Num_Voyage>0) ,

       

       

      // 1))

      // ) as "Statut Remorque1"

       

       

      // Resident final

      // Group by (month_dossier),"Statut Flotte",Num_Voyage,Plaque ;

       

      // /////////////////////////////////////////////////

      Concatenate

      Load *, if("Statut Remorque1"=1, 'Actif') as "Statut Remorque";

      load  (month_dossier),"Statut Flotte",Num_Voyage,Plaque,

      count(Num_Dossier) as Nombre_Traversée,

       

      sum(DISTINCT  if   (("Statut Flotte"=1 and Num_Voyage>0) ,

      1)

      ) as "Statut Remorque1"

       

      Resident final

      Group by (month_dossier),"Statut Flotte",Num_Voyage,Plaque  ;

      /////////////////////////////////////////////////

      Concatenate

      Load *, if("Statut Remorque1"=0, 'Non Actif') as "Statut Remorque";

       

      load  (month_dossier),"Statut Flotte",Num_Voyage,Plaque,

      count(Num_Dossier) as Nombre_Traversée,

       

      sum(DISTINCT  if   (("Statut Flotte"=1 and Num_Voyage=0) ,

      0)

      ) as "Statut Remorque1"

      Resident final

      Group by (month_dossier),"Statut Flotte",Num_Voyage,Plaque  ;

       

      Left Join (final)

      load * Resident tmp1;

       

      Drop Table tmp1;

       

       

      The result:

      bloqué: statut flotte=0

      Capture.PNG

       

       

      Actif: statut flotte=1 and numvoyage>0 :

      Capture.PNG

       

      Non active cassé: statut flotte =2 :

      Capture.PNG

       

      non active: statut flotte =1 and num voyage>0 : ERROR

      Capture.PNG

       

      What am I doing wrong? and is my approach correct?

      Sorry for the long message..

      Thank you !

        • Re: Help with script !
          Sunny Talwar

          What is nombre_traversée? I am not 100% sure I understand the logic of what you goal is... but may be you need to use Where clause instead of if statement right before you concatenate them into a single table. Again, I am not 100% sure I understand. Would you be able to share a sample?

          • Re: Help with script !
            Sébastien Fatoux

            Hi,

             

            For information, you don't have Num_Voyage = 0 when you filter your final data with Designation_Activite='RORO' and (Parc=('VECTORYS FRANCE') or Parc=('VECTORYS ITALIE')) , also you don't have 'Non Actif' as "Statut Remorque"

             

            Use your condition directly on Where clause like below:

             

             

            tmp1:
            load 
            (month_dossier),"Statut Flotte",Num_Voyage,Plaque,
            count(Num_Dossier)  as Nombre_Traversée,
            'bloquée'    as "Statut Remorque"
            Resident final
            Where "Statut Flotte"=0
            Group by (month_dossier),"Statut Flotte",Num_Voyage,Plaque ;

            ////////////////////////////////////////////
            Concatenate(tmp1)
            Load 
            (month_dossier),"Statut Flotte",Num_Voyage,Plaque,
            count(Num_Dossier)  as Nombre_Traversée,
            'Non active cassée' as "Statut Remorque"
            Resident final
            where "Statut Flotte"=2
            Group by (month_dossier),"Statut Flotte",Num_Voyage,Plaque ;

            /////////////////////////////////////////////////
            Concatenate(tmp1)
            Load 
            (month_dossier),"Statut Flotte",Num_Voyage,Plaque,
            count(Num_Dossier)  as Nombre_Traversée,
            'Actif'    as "Statut Remorque"
            Resident final
            Where "Statut Flotte"=1 and Num_Voyage>0
            Group by (month_dossier),"Statut Flotte",Num_Voyage,Plaque  ;

            /////////////////////////////////////////////////
            Concatenate(tmp1)
            Load 
            (month_dossier),"Statut Flotte",Num_Voyage,Plaque,
            count(Num_Dossier)  as Nombre_Traversée,
            'Non Actif'  as "Statut Remorque"
            Resident final
            Where "Statut Flotte"=1 and Num_Voyage=0
            Group by (month_dossier),"Statut Flotte",Num_Voyage,Plaque  ;

             

            Left Join(final)
            load * Resident tmp1;

            Drop Table tmp1;