5 Replies Latest reply: May 16, 2011 12:26 PM by Anosh Wadia RSS

    Iterate or Loop through records in a QlikView table

      Hi,

       

      This is my first post so my apologies if this is posted in the wrong location.

       

      I have an Excel file that I have loaded into a QlikView table. I would now like to iterate through the records in my QV table to create a new table that has the data in a format that my QV document needs.

       

      For example, my original Excel file (and the table in QV) have the following data:

      YEAR
      USERPRODUCTGOAL Q1GOAL Q2GOAL Q3GOAL Q4
      2011John DoeWidget 1100200150250
      2010John DoeWidget 2507585120
      2011Tom JonesWidget 120303540
      2011Tom JonesWidget 25678

       

       

      I want to transpose the data into the following format:

      YEARUSERQUARTERWIDGET 1 GOALWIDGET 2 GOAL
      2010John DoeQ1050
      2010John DoeQ2075
      2010John DoeQ3085
      2010John DoeQ40120
      2011John DoeQ11000
      2011John DoeQ22000
      2011John DoeQ31500
      2011John DoeQ42500
      2011Tom JonesQ1205
      2011Tom JonesQ2306
      2011Tom JonesQ3357
      2011Tom JonesQ4408

       

       

      I've made several attempts to transpose this data with no success.

       

      I was thinking I can loop through all available YEAR, USER and PRODUCT values and run nested loops to build my final table into the above format.

       

      So my algorithm would be:

       

       

      DestinationTable:

      For Each xYear in YearList

            For Each xUser in UserList

                  For Each xProduct in ProductList

                        For i = 1 to 4

                               Load

                                       Year,

                                        User

                                        'Q' & i as Quarter,

                                        [Widget 1 Goal],

                                        [Widget 2 Goal]

                              RESIDENT SourceTable

                              WHERE Product = xProduct and User = xUser and Year = xYear

                         Next i              

                  Next Product

            Next User

      Next Year

       

      However it seems like the FOR ....EACH statement does not seem to work with tables (it only works with arrays). So it looks like I'm using an entirely incorrect approach.

       

      I'd appreciate any advice the knowledgeable members of this community can provide.

       

      Thanks in advance!

      Anosh