0 Replies Latest reply: Jul 6, 2017 6:26 AM by Zied Ahmed RSS

    Data model problem

    Zied Ahmed

      Hello,

      I have two table F_details and F_Maroc and i need to have only one table (F_Details) and add the field Flag_Doublon in this  table. To calculate the Flag doublon i need the PLAQUE (DATE_EMBARQUEMENT&'-'&PLAQUE_REMORQUE as PLAQUE) when i use the code in this discussion  i have only the value 0 (please check the definiton of the field Flag_Doublon ) (if i use this code for example the Flag doublon work :


          NoConcatenate

          F_Maroc:

          load

       

          //     DATE_EMBARQUEMENT ,

      // PLAQUE_REMORQUE,

      DATE_EMBARQUEMENT&'-'&PLAQUE_REMORQUE as PLAQUE,

       

          if( Nombre_Carrier>1, 1, 0) as Flag_Doublon;

          Load

           DATE_EMBARQUEMENT ,

      PLAQUE_REMORQUE,

          count(CODE_CARRIER) as Nombre_Carrier

         resident  Fact_DW_Temp

          Where CODE_LIGNE = 'MAR'

          group by  DATE_EMBARQUEMENT ,

      PLAQUE_REMORQUE;

       

         drop table Fact_DW_Temp;

      )

       

      Capture.PNG

       

       

       

       

       

      And this is the code used now (Flag_Doublon = 0 )

       

      NoConcatenate

      Fact_DW_Temp:

      LOAD NUM_DOSSIER,

      CODE_CGV,

      //NUM_VOYAGE,

      //DATE_DOSSIER as Date_Dossier,

          DATE_DOSSIER,

      DATE_EMBARQUEMENT ,

      //DATE_DEBARQUEMENT,

      Parc,

         CODE_LIGNE,

          CODE_CARRIER,

      Activite as activite,

      //CODE_FILIALE_ORIGINE,

          CODE_SENS,

          CODE_COMPAGNIE_MARITIME,

      PLAQUE_REMORQUE;

      //CODE_FILIALE_DESTINATION,

      // Code_TYPE_MATERIEL;

       

       

       

       

      SELECT "NUM_DOSSIER",

      "NUM_VOYAGE",

              "CODE_SENS",

      "CODE_COMPAGNIE_MARITIME",

      "DATE_DOSSIER",

          "CODE_LIGNE",

              "CODE_CGV",

      "CODE_CARRIER",

      "DATE_EMBARQUEMENT",

      "DATE_DEBARQUEMENT",

      Parc,

      Activite,

      "CODE_FILIALE_ORIGINE",

      "PLAQUE_REMORQUE",

      "CODE_FILIALE_DESTINATION",

      "Code_TYPE_MATERIEL"

      FROM "VECTORYS_DATAWARHOUSE".DWH."DOSSIER_FAIT";

      ///////

       

       

      NoConcatenate

      F_Details:

       

       

      Load

       

       

      Date(DATE_DOSSIER, 'DD/MM/YYYY')&'-'&text(num(CODE_SENS))&'-'&text(num(CODE_COMPAGNIE_MARITIME))&'-'&text(activite)&'-'&text(CODE_LIGNE)&'-'&text(CODE_CARRIER)as [%Key ID],

             autonumber( date(DATE_DOSSIER, 'DD/MM/YYYY'),'%Date ID')  as [%Date ID],

      //          // CODE_COMPAGNIE_MARITIME,

            AutoNumber(CODE_COMPAGNIE_MARITIME,'%CODE_COMPAGNIE_MARITIME') as [%CODE_COMPAGNIE_MARITIME],

      //   //CODE_SENS,

            AutoNumber(CODE_SENS,'%CODE_SENS') as [%CODE_SENS],

            CODE_CARRIER,

             activite,

            CODE_LIGNE,

       

       

          CODE_CGV,

       

       

       

       

          DATE_EMBARQUEMENT ,

        PLAQUE_REMORQUE,

      //DATE_EMBARQUEMENT&'-'&PLAQUE_REMORQUE as PLAQUE,

       

       

          Parc,

       

       

       

        Flag_Doublon;

      load

      DATE_DOSSIER,

          CODE_CGV,

           CODE_SENS,

          CODE_COMPAGNIE_MARITIME,

          CODE_CARRIER,

          DATE_EMBARQUEMENT ,

      PLAQUE_REMORQUE,

          Parc,

          CODE_LIGNE,

          activite,

          if( Nombre_Carrier>1, 1, 0) as Flag_Doublon;

      LOAD

          DATE_DOSSIER,

          CODE_CGV,

           CODE_SENS,

          CODE_COMPAGNIE_MARITIME,

          CODE_CARRIER,

      DATE_EMBARQUEMENT ,

      PLAQUE_REMORQUE,

          Parc,

          CODE_LIGNE,

          activite,

          count(CODE_CARRIER) as Nombre_Carrier

       

          resident Fact_DW_Temp

          Where CODE_LIGNE = 'MAR'

          Group by DATE_DOSSIER,

           CODE_SENS,

          CODE_COMPAGNIE_MARITIME,

          CODE_CARRIER,

          CODE_CGV,

          DATE_EMBARQUEMENT ,

      PLAQUE_REMORQUE,

          Parc,

          CODE_LIGNE,

          activite;

        

         drop table Fact_DW_Temp;