5 Replies Latest reply: Sep 3, 2014 7:13 PM by Marco Wedel RSS

    Merge excel columns containing certain text?

    John-Harald Holmlund

      Is there a way to merge Excel columns containing a certain column header text when loading Excel table into Qlikview?

      I have a dynamicly changing Excel sheet with changing number of columns.

      Columnnames are buildt up by prefix, stem an postfix.

      Ex

      1_Answer_1123342, 2_Answer_213231, ...

       

      Is there an easy way to use wildcards or loops to concatenate all columns containing header text "Answer" into one Field?

        • Re: Merge excel columns containing certain text?
          Yousef Amarneh

          can you share an excel file contains a sample of your data and the column that you want to generate .

          • Re: Merge excel columns containing certain text?
            Stefan Kunte

            Hi,

             

            this script should work. But please note that this will only work when prefix and postfix do not change over several columns! 

             

            Columns:

            load

            @1 as Column1;

            LOAD *

            FROM

            sample.xlsx

            (ooxml, no labels, table is [My data], filters(

            Transpose()

            ));

             

            CleanCol:

            load

            '[' & concat(Column1, ']&[') & ']' as Old,

            ColumnClean

            group by subfield(del, '|', 2) ,ColumnClean;

            load

            subfield(subfield(Column1, ': ',2), '_',1) as ColumnClean,

            Column1 & '|' &subfield(subfield(Column1, ': ',2), '_',1) as del,

            Column1

            Resident Columns where not isnull(subfield(subfield(Column1, ': ',2), '_',1)) ;

             

             

            BaseTable:

            load

            RowNo() as NoRow,

            Date,

            DateTime,

            Location

            FROM

            sample.xlsx

            (ooxml, embedded labels, table is [My data]); 

             

             

             

            for i = 0 to (NoOfRows ('CleanCol') -1)

             

            let vColOld$(i) = peek ('Old', $(i), 'CleanCol');

             

            let vColNew$(i) = peek('ColumnClean', $(i), 'CleanCol');

             

             

            left join (BaseTable)

            load

            RowNo() as NoRow,

            $(vColOld$(i)) as [$(vColNew$(i))]

            FROM

            sample.xlsx

            (ooxml, embedded labels, table is [My data]); 

             

            next

             

             

            - Stefan

            • Re: Merge excel columns containing certain text?
              Marco Wedel

              Hi,

               

              one possible solution:

               

              QlikCommunity_Thread_131737_Pic1.JPG.jpg

               

              tabInput:
              CrossTable (FieldName, FieldValue, 3)
              LOAD * FROM [http://community.qlik.com/servlet/JiveServlet/download/600456-123094/sample.xlsx] (ooxml, embedded labels, table is [My data]);
              
              tabOutput:
              Generic LOAD
                DateTime,
                Location,
                FieldNameComb,
                Concat(FieldValue) as FieldValue
              Group By DateTime, Location, FieldNameComb;
              LOAD *, TextBetween(Replace(FieldName,'_',' '),' ',' ') as FieldNameComb
              Resident tabInput;
              
              DROP Table tabInput;
              

               

               

              hope this helps

               

              regards

               

              Marco