6 Replies Latest reply: Nov 23, 2016 2:50 AM by Hasvine Dhurmea RSS

    Merge column

    Hasvine Dhurmea

      Hello All,

       

      I have a table as below in excel:

         

      NameTel1Tel2Tel3
      A123456789
      B123456789456789321
      C123456789456789323
      D12345678945678932454784562
      E123456789 54784568
      F123456789 54784569
      G123456789
      H123456789

       

      And I need to add another column like to merge the tables as shown below:

       

        

      NameTEL
      A123456789
      B123456789
      B456789321
      C123456789
      C456789323
      D123456789
      D456789324
      D54784562
      E123456789
      E54784568
      F123456789
      F54784569
      G123456789
      H123456789

       

      Can you please advise me on the best method to do this?

       

      Many Thanks,

       

      Hasvine

        • Re: Merge column
          Tresesco B

          Try concatenating them loading separately like:

           

          Load

                    Name,

                    Tel1 as TEL

          From <>;

          Load

                    Name,

                    Tel2 as TEL

          From <>;

          Load

                    Name,

                    Tel3 as TEL

          From <>;

          • Re: Merge column
            Tamil Nagaraj

            Hi Hasvine,

             

            You can also use cross table. Kindly check the attached file.

             

            Data:
            CrossTable(Tel, Data)
            LOAD Name,
            Tel1,
            Tel2,
            Tel3
            FROM

            Excel.xlsx
            (
            ooxml, embedded labels, table is Sheet1);

            DROP Field Tel;

            RENAME Field Data To Tel;

              • Re: Merge column
                Hasvine Dhurmea

                Hello Tamil,

                 

                I am trying to load it as below but I dont seem to get it right:

                Can you help me with this?

                Note that my [Téléphone bureau] = Tel1

                                    [Téléphone domicile] = Tel2

                                    [Tél mobile] = Tel3

                 

                Data:

                CrossTable(Tel, Data)

                LOAD Titre,

                     Nom,

                     Prénom,

                     Service,

                     Titre1,

                     [Téléphone bureau],

                     [Téléphone domicile],

                     [Tél mobile],

                     [Adresse de messagerie],

                     CONCESSION

                FROM

                [..\Annuaire\SHLMR_ANNUAIRE.xls]

                (biff, embedded labels, table is [shlmr plateau caillou$]);

                 

                DROP Field Tel;

                 

                 

                RENAME Field Data To Tel;

                  • Re: Merge column
                    Tamil Nagaraj

                    Hasvine,

                     

                    Something like below.

                     

                    Data:

                    CrossTable(Téléphone, Data,7)

                    LOAD Titre,

                         Nom,

                         Prénom,

                         Service,

                         Titre1,

                         [Adresse de messagerie],

                         CONCESSION,

                         [Téléphone bureau],

                         [Téléphone domicile],

                         [Tél mobile],

                    FROM

                    [..\Annuaire\SHLMR_ANNUAIRE.xls]

                    (biff, embedded labels, table is [shlmr plateau caillou$]);

                     

                    DROP Field Téléphone;

                     

                    RENAME Field Data To Téléphone;

                    • Re: Merge column
                      Ganesh S

                      Hello Hasvine,

                       

                      Tamil Nagaraj did give you a proper solution, according to your data, you can use the following script:

                       

                      First, Identify the primary field in your table, suppose if, "Titre" is the PK, then,

                       

                      Table1:

                      LOAD Titre,

                           Nom,

                           Prénom,

                           Service,

                           [Adresse de messagerie],

                           CONCESSION

                      FROM

                      [..\Annuaire\SHLMR_ANNUAIRE.xls]

                      (biff, embedded labels, table is [shlmr plateau caillou$]);

                       

                      Table2:

                      CrossTable(Tel, Data)

                      LOAD Titre,

                           [Téléphone bureau],

                           [Téléphone domicile],

                           [Tél mobile]

                      FROM

                      [..\Annuaire\SHLMR_ANNUAIRE.xls]

                      (biff, embedded labels, table is [shlmr plateau caillou$]);

                       

                      DROP Field Tel;

                      RENAME Field Data To Tel;

                       

                      Then, you can Join or concatenate both table or just leave it which will associate itself with key field.

                       

                      Regards,

                      Ganesh