13 Replies Latest reply: Mar 14, 2017 12:01 PM by Sunny Talwar RSS

    modify column name

    souad ouertani

      I want to avoid this structure for column names

      script:

       

       

      Qualify *;

      [DEPOTCOMPTEF]:

      LOAD

        'COMPTES' AS PRODUIT,

          ANNEE & '-' & IF(MOIS ='janv.','01',IF(MOIS='févr.','02',IF(MOIS='mars','03',IF(MOIS='avr.','04',IF(MOIS='mai','05',IF(MOIS='juin','06',IF(MOIS='juil.','07',IF(MOIS='août','08',IF(MOIS='sept.','09',IF(MOIS='oct.','10',IF(MOIS='nov.','11','12'))))))))))) AS SITUATIONDEPOTCOMPTE,

           STAUT_CLIENT AS STATUT_CLIENT,

           MARCHE,

            DESCRIPTION_MARCHE_CLIENT,

           %ID_AGENCE_CTOS_COMPTE,

           %ID_AGENCE_CTOS_COMPTE AS AGENCEDEPOTCOMPTE, 

           AGE_RELATION,

           DESCRIPTION_CATEGORIE_CLIENT,

           %ID_CATEGORIE_CLIENT,

           %ID_DEVISE_COMPTE ,

           NATURE_COMPTE ,

           DESCRIPTION_TYPE_DEPOT_COMPTE ,

           DESCRIPTION_SOUS_TYPE_DEPOT_COMPTE ,

           %ID_TYPE_DEPOT_COMPTE ,

           %ID_SOUS_TYPE_DEPOT_COMPTE ,

           PROD_COMPTE ,

           STATUT_COMPTE ,

           SEGMENT,

           0 AS ATTRITION,

           CONQUETE,

           ANCIENNETE_RELATION,

           VENTE_COMPTE ,

           STOCK_COMPTE ,

         

            SOUS_MARCHE,

           TYPE_SOUS_MARCHE,

           SEGMENT_2015 as SEGMENT_N_1

         

      From

      C:\pfe\DONNEES_RECAP\DEPOTS_COMPTE.qvd

      (qvd);

      Qualify *;

      [DEPOTCOMPTE]:

      LOAD *,

        RowNo() as tDEPOTCOMPTE_id

      Resident [DEPOTCOMPTEF];

       

       

      DROP Table DEPOTCOMPTEF;

       

       

       

      colonne.PNG

        • Re: modify column name
          Aar Kay

          May be this:

          Qualify * ;

          Unqualify ColumnName1,CoumnName2;

          load *

               From table;

            • Re: modify column name
              souad ouertani

              didnt work

                • Re: modify column name
                  Aar Kay

                  Qualify *;

                  Unqualify AGE_RELATION,%ID_CATEGORIE_CLIENT,DESCRIPTION_SOUS_TYPE_DEPOT_COMPTE ;

                  [DEPOTCOMPTEF]:

                  LOAD

                    'COMPTES' AS PRODUIT,

                      ANNEE & '-' & IF(MOIS ='janv.','01',IF(MOIS='févr.','02',IF(MOIS='mars','03',IF(MOIS='avr.','04',IF(MOIS='mai','05',IF(MOIS='juin','06',IF(MOIS='juil.','07',IF(MOIS='août','08',IF(MOIS='sept.','09',IF(MOIS='oct.','10',IF(MOIS='nov.','11','12'))))))))))) AS SITUATIONDEPOTCOMPTE,

                       STAUT_CLIENT AS STATUT_CLIENT,

                       MARCHE,

                        DESCRIPTION_MARCHE_CLIENT,

                       %ID_AGENCE_CTOS_COMPTE,

                       %ID_AGENCE_CTOS_COMPTE AS AGENCEDEPOTCOMPTE,

                       AGE_RELATION,

                       DESCRIPTION_CATEGORIE_CLIENT,

                       %ID_CATEGORIE_CLIENT,

                       %ID_DEVISE_COMPTE ,

                       NATURE_COMPTE ,

                       DESCRIPTION_TYPE_DEPOT_COMPTE ,

                       DESCRIPTION_SOUS_TYPE_DEPOT_COMPTE ,

                       %ID_TYPE_DEPOT_COMPTE ,

                       %ID_SOUS_TYPE_DEPOT_COMPTE ,

                       PROD_COMPTE ,

                       STATUT_COMPTE ,

                       SEGMENT,

                       0 AS ATTRITION,

                       CONQUETE,

                       ANCIENNETE_RELATION,

                       VENTE_COMPTE ,

                       STOCK_COMPTE ,

                    

                        SOUS_MARCHE,

                       TYPE_SOUS_MARCHE,

                       SEGMENT_2015 as SEGMENT_N_1

                    

                  From

                  C:\pfe\DONNEES_RECAP\DEPOTS_COMPTE.qvd

                  (qvd);

                  Qualify *;

                  [DEPOTCOMPTE]:

                  LOAD *,

                    RowNo() as tDEPOTCOMPTE_id

                  Resident [DEPOTCOMPTEF];

                   

                   

                  DROP Table DEPOTCOMPTEF;

              • Re: modify column name
                Carlos Monroy

                Hi Souad,

                 

                You have two 'Qualify'. That's causing the problem.

                 

                One solution, just leave one qualify, the very first one. if you do not want the next table to qualify the fields use unqualify *;

                 

                If you just want to unqualify some fields, replace the star for the field name.

                 

                Hope that helps,

                Carlos M

                 

                Qualify *;

                [DEPOTCOMPTEF]:

                LOAD

                  'COMPTES' AS PRODUIT,

                    ANNEE & '-' & IF(MOIS ='janv.','01',IF(MOIS='févr.','02',IF(MOIS='mars','03',IF(MOIS='avr.','04',IF(MOIS='mai','05',IF(MOIS='juin','06',IF(MOIS='juil.','07',IF(MOIS='août','08',IF(MOIS='sept.','09',IF(MOIS='oct.','10',IF(MOIS='nov.','11','12'))))))))))) AS SITUATIONDEPOTCOMPTE,

                    STAUT_CLIENT AS STATUT_CLIENT,

                    MARCHE,

                      DESCRIPTION_MARCHE_CLIENT,

                    %ID_AGENCE_CTOS_COMPTE,

                    %ID_AGENCE_CTOS_COMPTE AS AGENCEDEPOTCOMPTE,

                    AGE_RELATION,

                    DESCRIPTION_CATEGORIE_CLIENT,

                    %ID_CATEGORIE_CLIENT,

                    %ID_DEVISE_COMPTE ,

                    NATURE_COMPTE ,

                    DESCRIPTION_TYPE_DEPOT_COMPTE ,

                    DESCRIPTION_SOUS_TYPE_DEPOT_COMPTE ,

                    %ID_TYPE_DEPOT_COMPTE ,

                    %ID_SOUS_TYPE_DEPOT_COMPTE ,

                    PROD_COMPTE ,

                    STATUT_COMPTE ,

                    SEGMENT,

                    0 AS ATTRITION,

                    CONQUETE,

                    ANCIENNETE_RELATION,

                    VENTE_COMPTE ,

                    STOCK_COMPTE ,

                 

                      SOUS_MARCHE,

                    TYPE_SOUS_MARCHE,

                    SEGMENT_2015 as SEGMENT_N_1

                 

                From

                C:\pfe\DONNEES_RECAP\DEPOTS_COMPTE.qvd

                (qvd);

                 

                Qualify *;

                unqualify fieldname1, fieldname2, etc;

                 

                [DEPOTCOMPTE]:

                LOAD *,

                  RowNo() as tDEPOTCOMPTE_id

                Resident [DEPOTCOMPTEF];

                 

                 

                DROP Table DEPOTCOMPTEF;

                • Re: modify column name
                  Marcus Sommer

                  You could just put the rowno() within the first load whereby do you really this rowno as ID? Further do you really need to qualify all fields?

                   

                  - Marcus

                  • Re: modify column name
                    Adam Davies

                    I agree with Marcus that this seems a little odd, but who are we to judge!

                    However you just need to make sure that your're using the NEW field name when doing the unqualify, you can't just use the original ones.

                    So this should work:

                     

                    Qualify *;

                    [DEPOTCOMPTEF]:

                    LOAD

                      'COMPTES' AS PRODUIT,

                        ANNEE & '-' & IF(MOIS ='janv.','01',IF(MOIS='févr.','02',IF(MOIS='mars','03',IF(MOIS='avr.','04',IF(MOIS='mai','05',IF(MOIS='juin','06',IF(MOIS='juil.','07',IF(MOIS='août','08',IF(MOIS='sept.','09',IF(MOIS='oct.','10',IF(MOIS='nov.','11','12'))))))))))) AS SITUATIONDEPOTCOMPTE,

                        STAUT_CLIENT AS STATUT_CLIENT,

                        MARCHE,

                          DESCRIPTION_MARCHE_CLIENT,

                        %ID_AGENCE_CTOS_COMPTE,

                        %ID_AGENCE_CTOS_COMPTE AS AGENCEDEPOTCOMPTE,

                        AGE_RELATION,

                        DESCRIPTION_CATEGORIE_CLIENT,

                        %ID_CATEGORIE_CLIENT,

                        %ID_DEVISE_COMPTE ,

                        NATURE_COMPTE ,

                        DESCRIPTION_TYPE_DEPOT_COMPTE ,

                        DESCRIPTION_SOUS_TYPE_DEPOT_COMPTE ,

                        %ID_TYPE_DEPOT_COMPTE ,

                        %ID_SOUS_TYPE_DEPOT_COMPTE ,

                        PROD_COMPTE ,

                        STATUT_COMPTE ,

                        SEGMENT,

                        0 AS ATTRITION,

                        CONQUETE,

                        ANCIENNETE_RELATION,

                        VENTE_COMPTE ,

                        STOCK_COMPTE ,

                     

                          SOUS_MARCHE,

                        TYPE_SOUS_MARCHE,

                        SEGMENT_2015 as SEGMENT_N_1

                     

                    From

                    C:\pfe\DONNEES_RECAP\DEPOTS_COMPTE.qvd

                    (qvd);

                     

                    unqualify *;

                     

                    Qualify *;

                     

                    UNQUALIFY DEPOTCOMPTEF.AGE_RELATION, DEPOTCOMPTEF. %ID_CATEGORIE_CLIENT, DEPOTCOMPTEF.DESCRIPTION_SOUS_TYPE_DEPOT_COMPTE;

                     

                    [DEPOTCOMPTE]:

                    LOAD *,

                      RowNo() as tDEPOTCOMPTE_id

                    Resident [DEPOTCOMPTEF];

                     

                    unqualify *;

                     

                    DROP Table DEPOTCOMPTEF;

                    • Re: modify column name
                      Sunny Talwar

                      What exactly are you trying to get rid of here? Remove DEPOTCOMPTEF from all your field names or the ones that you highlighted in Yellow?

                        • Re: modify column name
                          souad ouertani

                          I do not want to display the column name with a long hierarchy  but i found the solution now it works correctly

                           

                           

                          Qualify *;

                          [PACKF]:

                          LOAD 

                           

                           

                          'PACKS' AS PRODUIT,

                              %ID_AGENCE_CTOS_PACK,

                              ANNEE & '-' & IF(MOIS ='janv.','01',IF(MOIS='févr.','02',IF(MOIS='mars','03',IF(MOIS='avr.','04',IF(MOIS='mai','05',IF(MOIS='juin','06',IF(MOIS='juil.','07',IF(MOIS='août','08',IF(MOIS='sept.','09',IF(MOIS='oct.','10',IF(MOIS='nov.','11','12'))))))))))) AS SITUATIONPACK, 

                               STAUT_CLIENT AS STATUT_CLIENT,

                               MARCHE,

                               DESCRIPTION_MARCHE_CLIENT,

                                %ID_AGENCE_CTOS_PACK AS AGENCEPACK,

                               AGE_RELATION,

                               DESCRIPTION_CATEGORIE_CLIENT,

                               %ID_CATEGORIE_CLIENT,

                               %ID_TYPE_PACK ,

                               LIB_TYPE_PACK ,

                               SEGMENT,

                               'PACK' AS TYPE,

                              CONQUETE,

                               ANCIENNETE_RELATION,

                               NOMBRE_PACK ,

                             

                             

                               0 AS PROD_MOIS,

                               STATUT_PACK AS STATUT_PROD,

                                'PACK' AS NATURE,

                            

                            

                                SOUS_MARCHE,

                               TYPE_SOUS_MARCHE,

                               SEGMENT_2015 as SEGMENT_N_1

                          FROM

                          [C:\pfe\DONNEES_RECAP\PACKS.qvd]

                          (qvd);

                          Qualify *;

                          [PACKS]:

                          LOAD *,

                            RowNo() as tPACK_id

                          Resident [PACKF];

                           

                           

                          DROP Table PACKF;