4 Replies Latest reply: Aug 27, 2014 6:19 PM by Marco Wedel RSS

    Read cell value and append it to an additional column?

    yuntao peng

      Hello everyone,

      I have encountered a situation here:

      There are over 40 excel files with similar names (CompanyX 07 2014.xlsx) in a folder, and I need to load all of them .

      The header are on line 7 but I have to read a cell value (B2, it's the name of the company) and add it as the value of a auto generated column.

       

      How can I achieve it ?

       

      Thank you a lot!

        • Re: Read cell value and append it to an additional column?
          Marco Wedel

          Hi,

           

          define the source filenames with wildcards like "*.xlsx".

          Furthermore you might read your company names from the filename also:

           

          tabData:
          LOAD Employee,
              Structure,
              fees,
              [Type of fees],
              Month,
              [Total TTC],
              Left(FileBaseName(), Len(FileBaseName())-8) as Company
          FROM
          //[http://community.qlik.com/servlet/JiveServlet/download/596844-122156/test%20companyA%2007%202014.xlsx]
          [C:\Daten\Qlikview\QlikCommunity\QlikCommunity_Thread_131051\*.xlsx]
          (ooxml, embedded labels, table is Example, filters(
          Remove(Row, Pos(Top, 4)),
          Remove(Row, Pos(Top, 3)),
          Remove(Row, Pos(Top, 2)),
          Remove(Row, Pos(Top, 1)),
          Remove(Row, Pos(Top, 2)),
          Remove(Row, Pos(Top, 2)),
          Remove(Row, Pos(Top, 2)),
          Remove(Row, Pos(Top, 4))
          ));
          

           

          QlikCommunity_Thread_131051_Pic1.JPG.jpg

           

          QlikCommunity_Thread_131051_Pic2.JPG.jpg

           

          hope this helps

           

          regards

           

          Marco

          • Re: Read cell value and append it to an additional column?
            Marco Wedel

            Hi again,

             

            another possibility is to load the company from the file content seperate from the table and to join the two loads afterwards:

             

            tabData:
            LOAD Employee,
                Structure,
                fees,
                [Type of fees],
                Month,
                [Total TTC],
                Left(FileBaseName(), Len(FileBaseName())-8) as CompanyFromFileName,
                FileBaseName() as File
            FROM
            //[http://community.qlik.com/servlet/JiveServlet/download/596844-122156/test%20companyA%2007%202014.xlsx]
            [C:\Daten\Qlikview\QlikCommunity\QlikCommunity_Thread_131051\*.xlsx]
            (ooxml, embedded labels, table is Example, filters(
            Remove(Row, Pos(Top, 4)),
            Remove(Row, Pos(Top, 3)),
            Remove(Row, Pos(Top, 2)),
            Remove(Row, Pos(Top, 1)),
            Remove(Row, Pos(Top, 2)),
            Remove(Row, Pos(Top, 2)),
            Remove(Row, Pos(Top, 2)),
            Remove(Row, Pos(Top, 4))
            ));
            
            tabTemp:
            LOAD B as Company,
                FileBaseName() as File
            From
            [C:\Daten\Qlikview\QlikCommunity\QlikCommunity_Thread_131051\*.xlsx]
            (ooxml, no labels, table is Example)
            Where RecNo() = 1;
            
            Left Join (tabData)
            LOAD *Resident tabTemp;
            
            DROP Table tabTemp;
            
            

             

            QlikCommunity_Thread_131051_Pic3.JPG.jpg

             

            hope this helps

             

            regards

             

            Marco