12 Replies Latest reply: Aug 2, 2018 5:34 AM by Florian Blum RSS

    Merge fields

    Vincent Bellanger

      Greetings,

       

      I've a table setup like this :

      IDDefinitionName
      1First NameAlexander
      2Last NameWise
      3First NameChris
      4Last NameDamico
      5First NameFrederick
      6Last NameLeviathan

       

      And i'm wondering if there is a way to merge them to have a table like this:

      IDName
      1Alexander Wise
      3Chris Damico
      5Frederick Leviathan

       

      And will i have to create a new table for this ? or is it possible to change the first one ?

       

      Thanks for answer/tips

        • Re: Merge fields
          Gabor Tarnoczai

          Hi vincent,

           

          Try this inload script:

          (so the ID 1 match ID 2):

           

          table1:

          Load

               ID,

               Name

          From/Resident Table

          Where mod(id,2)=1;

           

          left join(table1)

          Load

               ID-1 as ID,

               Name as Name2

          From/Resident Table

          Where mod(id,2)=0;

           

          table_final:

          Noconcatenate

          Load

               ID,

               Name&' '&Name_2 as Name

          resident table1;

           

          drop table table1;

           

          If you mistyped , and the data look like this:

          1 First_name Andrew

          1 Last_name Smith

           

          then

          table1:

          Load

               ID,

               Name as Firstname

          From/Resident Table

          Where Definition = 'First Name';

           

          Left join(table1)

          Load

               ID,

               Name as Lastname

          From/Resident Table

          Where Definition = 'Last Name';

           

          Load

               ID,

               Firstname&' '&Lastname as Name

          resident table1;

           

          drop table table1;

           

           

          Hope this helps.

           

          G.

          • Re: Merge fields
            Gerold Roser

            hello

            using  above ()

            Is the firstname and Lastname allways correctly sorted

            1 firstname 1

            2 lastname 1

            3 firstname 2

            4 lastname 2

            etc......

             

            ??

              • Re: Merge fields
                Vincent Bellanger

                No not always sadly i forgot to mention that!

                  • Re: Merge fields
                    Gabor Tarnoczai

                    but the consecutive ID-s, match right?

                    1 with 2, 3 with 4?

                     

                    Because after an ordering Gerold's answer can be good, after filter out the 2-3, 4-5 pairs.

                     

                    G.

                      • Re: Merge fields
                        Vincent Bellanger

                        No they are not sometime it can be like this

                        1 Firstname Alex

                        2 Firstname Jean

                        3 Lastname Leviathan

                         

                        Some of them didn't put their last name for example

                        I'm sorting them by a registrary number like this

                         

                        1 156   Firstname Alex

                        2 1155 Firstname Jean

                        3 1155 Lastname Leviathan

                         

                        So the matching pair would always have the same Registrary number

                        Maybe something like this would be easier to get ?

                         

                        1 156    Alex

                        2 1155 Jean Leviathan

                          • Re: Merge fields
                            Massimo Grossi

                            Try this

                            1.png

                             

                            input:

                            load * inline [

                            rowno registrarynumber type value

                            1 156   Firstname Alex

                            2 1155 Firstname Jean

                            3 1155 Lastname Leviathan

                            4 100 Firstname Max

                            5 100 Lastname Gro

                            ] (delimiter is spaces)

                            ;

                             

                            output:

                            load registrarynumber, value as First Resident input where type = 'Firstname';

                            join (output) load registrarynumber, value as Last Resident input where type = 'Lastname';

                            • Re: Merge fields
                              Gabor Tarnoczai

                              I see, so you have a key to match

                              then follow this logic:

                               

                              table_tmp:

                              load

                                   registrary_number,

                                   Name as First

                              from [table]

                              where Definition='First name';

                               

                              left join(table_tmp)

                              Load

                                   registrary_number,

                                   Name as Last

                              from [table]

                              where Definition='Last name';

                               

                              Table_final:

                              Load

                                   registrary_number,

                                  First&' '&Last as Name

                              Resident table_tmp;

                               

                              drop table table_tmp;

                               

                              G.

                        • Re: Merge fields
                          Gerold Roser

                           

                          hello Vicent

                          here is a example you are copy  the coding in a New qvw

                           

                           

                          greetings gerrold

                           

                          // Example

                           

                           

                          ReadTable:
                          load * inline [
                          ID, Definiton,Name
                          1, FirstName, Alexander
                          2, LastName, Wise
                          3, FirstName, Chris
                          4, LastName, Damico
                          5, FirstName, Frederick
                          6, LastName, Leviathan
                          ]
                          ;

                          EndTable:
                          load *
                          where not FirstLastName = 'Del'
                          ;
                          load 
                          if (Definiton  = 'LastName' , previous(Name)&','&Name,'Del') as FirstLastName ,
                          previous(ID) as ID
                          resident ReadTable;


                          drop Table ReadTable;

                            • Re: Merge fields
                              Gerold Roser

                              here the expample as qvw

                               

                              :-)

                              Gerry

                                • Re: Merge fields
                                  Gerold Roser

                                  here a script is not First and last Name

                                   

                                  greetings Gerry

                                   

                                   

                                  ReadTable:
                                  load * inline [
                                  ID, Definition,Name
                                  1, FirstName, Alexander
                                  2, LastName, Wise
                                  3, FirstName, Chris
                                  4, LastName, Damico
                                  5, FirstName, Frederick
                                  6, LastName, Leviathan
                                  7, LastName, NewName
                                  8, LastName, NewName2
                                  ]
                                  ;

                                  EndTable:
                                  load *
                                  where not FirstLastName = 'Del'
                                  ;
                                  load 
                                  if (Definition  = 'LastName' and Previous(Definition) = 'FirstName' , previous(Name)&', '&Name,
                                       if (Definition  = 'LastName' and Previous(Definition) = 'LastName', 'noFirstname'&', '&Name , 'Del')) as FirstLastName ,

                                  if (Definition  = 'LastName' and Previous(Definition) = 'FirstName' , previous(ID),
                                        if (Definition  = 'LastName' and Previous(Definition) = 'LastName', ID , 'Del')) as ID_NEW

                                  resident ReadTable;


                                  drop Table ReadTable;

                                    • Re: Merge fields
                                      Gabor Tarnoczai

                                      Hi Gerold,

                                       

                                      It is okay, but VIncent post an extra information since,

                                      So the matching pair would always have the same Registrary number

                                      there can be the case the rownumbers are not consecutives, but the registrary number is the key for match.

                                       

                                      G.

                              • Re: Merge fields
                                Florian Blum

                                Either the IDs must match, IDs must be assigned in descending order. For this a solution has already been posted or the sorting has to fit

                                 

                                for correct sorting the script would look like that:

                                LOAD previous(ID) as ID

                                           previous(Name)&' '&Name as field

                                Resident tablename

                                Where Definition='Last Name';