12 Replies Latest reply on Oct 16, 2013 7:48 AM by Prasanth Gopinath

    filter on row number in excel sheet

    Prasanth Gopinath

      I am trying to extract data from an excel sheet into Tableau by applying a row filter. What I need is to specify a row number and extract everything after that row number in the excel sheet. Has anyone been able to do this with an excel sheet? Your help is greatly appreciated.

        • 1. Re: filter on row number in excel sheet
          Alex Kerin

          Presumably you have access to the excel file to add another column?

           

          Add =row() to that column, then use the filter on the extract.

          • 2. Re: filter on row number in excel sheet
            Prasanth Gopinath

            Thanks for the response Alex. Unfortunately I cannot add a column to the sheet. Is it possible to add a "where" filter to the custom sql for an excel sheet to say pick the rows greater than a particular row number?

             

            SELECT [Sheet1$].[Acrivations] AS [Acrivations],

              [Sheet1$].[Date] AS [Date],

              [Sheet1$].[m1] AS [m1],

              [Sheet1$].[m10] AS [m10],

              [Sheet1$].[m11] AS [m11],

              [Sheet1$].[m12] AS [m12],

              [Sheet1$].[m13] AS [m13],

              [Sheet1$].[m14] AS [m14],

              [Sheet1$].[m15] AS [m15],

              [Sheet1$].[m16] AS [m16],

              [Sheet1$].[m17] AS [m17],

              [Sheet1$].[m18] AS [m18],

              [Sheet1$].[m19] AS [m19],

              [Sheet1$].[m2] AS [m2],

              [Sheet1$].[m20] AS [m20],

              [Sheet1$].[m21] AS [m21],

              [Sheet1$].[m22] AS [m22],

              [Sheet1$].[m23] AS [m23],

              [Sheet1$].[m24] AS [m24],

              [Sheet1$].[m25] AS [m25],

              [Sheet1$].[m26] AS [m26],

              [Sheet1$].[m27] AS [m27],

              [Sheet1$].[m28] AS [m28],

              [Sheet1$].[m29] AS [m29],

              [Sheet1$].[m3] AS [m3],

              [Sheet1$].[m30] AS [m30],

              [Sheet1$].[m4] AS [m4],

              [Sheet1$].[m5] AS [m5],

              [Sheet1$].[m6] AS [m6],

              [Sheet1$].[m7] AS [m7],

              [Sheet1$].[m8] AS [m8],

              [Sheet1$].[m9] AS [m9],

              [Sheet1$].[reportname] AS [reportname]

            FROM [Sheet1$]

            • 3. Re: filter on row number in excel sheet
              Dan Huff

              Does selecting Import Some Data and then setting a Top # of rows not work in your case? In the context of excel, Top should just grab the rows in order of the excel file.

               

              Top.png

              • 4. Re: filter on row number in excel sheet
                Prasanth Gopinath

                I need to pick rows starting after the the 90th row for example. Selecting the top rows so may not work in this case.

                • 5. Re: filter on row number in excel sheet
                  Alex Kerin

                  No, not that I can think off - is there anything past row 90 that makes the data special - why are you trying to filter it?

                  • 6. Re: filter on row number in excel sheet
                    Prem Reddy

                    Hi Prasanth,

                     

                    If I've understood your scenario correctly then while extracting records from Excel you have the option of adding filters on your dimensions or measures and in your case you can simple specify the range value from 90 ending all the way till the end value.

                     

                    Range Value.png

                    If you want to exclude null values then you can avail the "special" option available.

                     

                    Thanks,

                    Prem

                    • 7. Re: filter on row number in excel sheet
                      Alex Kerin

                      That only works if you have a rowid column, which Prasanth cannot add

                      • 8. Re: filter on row number in excel sheet
                        Matt Lutton

                        I'm not sure if its feasible with the dataset in question, but is it possible to import all the data, create a "row ID" by creating an INDEX() calculated field after all the data is inside Tableau--and then using that (with proper sort/compute using settings in a view) to filter out the first 90 rows in a Data Source Filter?  I have no idea how large the dataset is, but I'm just thinking aloud and am curious if that could work.

                        • 9. Re: filter on row number in excel sheet
                          Joshua Milligan

                          Prasanth,

                           

                          Using custom SQL, you can write something like this to get everything after Row 5 (for example):

                           

                          SELECT [Sheet1$].[City] AS [City],
                            [Sheet1$].[Customer Name] AS [Customer Name],
                            [Sheet1$].[Customer Segment] AS [Customer Segment],
                            [Sheet1$].[Sales] AS [Sales]
                          FROM [Sheet1$]
                          LEFT JOIN
                          (
                            SELECT TOP 5 [Sheet1$].[City] AS [City],
                              [Sheet1$].[Customer Name] AS [Customer Name],
                              [Sheet1$].[Customer Segment] AS [Customer Segment],
                              [Sheet1$].[Sales] AS [Sales]
                            FROM [Sheet1$]
                          ) s on
                            s.City = [Sheet1$].[City]
                          AND s.[Customer Name] = [sheet1$].[Customer Name]
                          AND s.[Customer Segment] = [sheet1$].[Customer Segment]
                          AND s.[Sales]  = [sheet1$].[Sales]
                          WHERE s.City IS NULL
                          
                          

                           

                          What this does it to create a LEFT self-join in which the TOP N rows get a match, but are then excluded by the WHERE IS NULL clause.  It's a little tedious, because you'll have to join on every field that defines a unique record.  That can be a lot of fields (possibly every field), unless you have an identify field or some kind of unique identifier.

                           

                          I've attached a sample workbook to demonstrate how this could work.  I did have some issues parameterizing the query and suspect I've hit a few bugs.  I'm going to spend a little more time on that.

                           

                          Regards,

                          Joshua

                          • 10. Re: filter on row number in excel sheet
                            Alex Kerin

                            I suspected you could do this with SQL. Nice job Joshua.

                            • 11. Re: filter on row number in excel sheet
                              Joshua Milligan

                              Thanks Alex!

                               

                              A couple more notes on this approach:

                              1. If even every field does not define a unique record, it won't work as it will possibly filter out multiple matches.
                              2. The WHERE s.City IS NULL  should use a non-nullable field

                               

                              -Joshua

                              • 12. Re: filter on row number in excel sheet
                                Prasanth Gopinath

                                Thank you all. You guys have been tremendously helpful.