3 Replies Latest reply: Oct 28, 2011 12:15 AM by Kamal Naithani RSS

    Loading Multiple Tables from 1 Excel Sheet into QV

      Dear All,

      I have a case im facing that in one sheet i have different multiple tables scattered in different areas in the same sheet that i need to load in QV

      Most of the tables have calculations connecting the tables together.

       

      find attached image of the tables in one sheet ( i had to zoom out in order to show most of the tables and how they are scattered)

        • Loading Multiple Tables from 1 Excel Sheet into QV
          Kaushik Solanki

          Hi,

           

              There are no direct way to load them into QV. One way is you use the tranformation of the QlikView (which you find in a wizard when you load the excel data. i.e File Wizard). Other way is to do changes in excel itself i.e keep only one table in one sheet of the excel.

           

             Hope this will help you a bit.

           

          Regards,

          Kaushik Solanki

          • Loading Multiple Tables from 1 Excel Sheet into QV
            Steve Dark

            Hi Moreed,

             

            You need to know how the data is anchored to the sheet, ie. does each separate table always start in the same cell.  If so you can specify on the load the number of rows to skip to find the first row of data for each table and then use the FIRST prefix to only pull in a certain number of rows.  If these positions are not fixed though things will become more tricky.

             

            If tables are not always in the same position you could pull all of the data into one large QV table and then attempt to chunk it up into the correct tables with a number of resident loads.  The way I can see you could try approaching this is by stamping the row number on each row when pulling from the spreadsheet.  You could then use peek or lookup statements to find the row numbers where there are blank lines (denoting the end of one table and the start of the next), from this you could then work out which row numbers you need to load into each of your tables.  These would then be resident loads with where statements on the row numbers.

             

            Hope that makes some kind of sense - feel free to bounce back further questions.

             

            Steve

            www.quickintelligence.co.uk