6 Replies Latest reply: Jan 5, 2018 3:40 AM by Gareth Cox-Thorpe RSS

    Merge fields

    Gareth Cox-Thorpe

      Hi all,

       

      I have an excel file that I'm loading into QV. There is a number of fields that have the same data and I am wanting to group these fields into one. Each row entry has a unique field.

       

      Example.

       

      Motor Topic
      Home Topic
      Pet Topic
      Travel Topic
      Amend
      Sale
      Sale
      Renew

       

      Aim.

       

      Topic
      Amend
      Sale
      Renew
      Sale

       

       

      Any help would be much appreciated.

       

      Thanks a lot

       

      gwassenaar

      hic

      stevedark

      rwunderlich

      jimhalpert

        • Re: Merge fields
          Henric Cronström

          I would probably use a Crosstable Load.

           

          The Crosstable Load

           

          HIC

          • Re: Merge fields
            Chanty 4u

            try this

             

            a:

            LOAD [Motor Topic]& ''& [Home Topic] &''&[Pet Topic] & ''& [Travel Topic] as Topic

            FROM

            [C:\Users\test\Desktop\Book2.xls]

            (ooxml, embedded labels, table is Sheet1);

            • Re: Merge fields
              Chanty 4u

              tested with conc.PNG   because in list box you will get unique values thats why sales come once.

              • Re: Merge fields
                Steve Dark

                Hi Gareth,

                 

                Simply load from it four times;

                 

                LOAD

                    OtherFields,

                     'Motor' as [Topic Type],

                    [Motor Topic] as Topic

                FROM ExcelFile.xlsx;

                 

                LOAD

                    OtherFields,

                     'Home' as [Topic Type],

                    [HomeTopic] as Topic

                FROM ExcelFile.xlsx;

                 

                LOAD

                    OtherFields,

                     'Pet' as [Topic Type],

                    [Pet Topic] as Topic

                FROM ExcelFile.xlsx;

                 

                LOAD

                    OtherFields,

                     'Travel' as [Topic Type],

                    [Travel Topic] as Topic

                FROM ExcelFile.xlsx;

                 

                 

                Make sure the field list is identical each time and auto-concatenation will take place.

                 

                You could get cleverer with it, and load the list of fields into a table and enumerate around them.

                 

                A CROSSTABLE load will also do it for you, but if it is just four fields the way above may be cleaner.


                The code for CROSSTABLE is:

                 

                CROSSTABLE ([Topic Type], Topic, 3)

                LOAD
                    *

                FROM ExcelFile.xlsx;

                 

                The number 3 relates to how many fields there are in the table left of the first topic field.  It relies on all other columns in the spreadsheet being to the left of the topic columns.  You can force this by listing the fields in a certain order, rather than using a * (but a * allows for new topics without changing the script).

                 

                Hope that helps.


                Steve

                • Re: Merge fields
                  Amit Saini

                  Like this ???
                  PFA!

                  • Re: Merge fields
                    Gareth Cox-Thorpe

                    Thanks everyone, Steve Dark's suggested worked!