Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

1 Solution

Accepted Solutions
nagaiank
Specialist III
Specialist III

What you have is a pivot table with four dimensions (Year, User, Product, and Quarter) and one fact (Goal).

What you want also is a pivot table with the same four dimensions and one fact with the position of dimensions Product and Quarter changed.

You may load the source pivot table into a Qlikview logical table using the table load wizard, which generates a script like this one below and then generate the second pivot table or any other pivot table in the format you need.

CrossTable(Q, Data, 3)

LOAD YEAR,

     USER,

     PRODUCT,

     [GOAL Q1],

     [GOAL Q2],

     [GOAL Q3],

     [GOAL Q4]

FROM

C:\Users\Kris\Desktop\pivot.xlsx

(ooxml, embedded labels, table is Sheet1);

Hope this helps.

View solution in original post

5 Replies
IAMDV
Luminary Alumni
Luminary Alumni

Anosh - Have you considered transposing the data in QV Load Script?

Thanks - DV

Not applicable
Author

Thanks for your response.

Yes that's what I was meaning to do ....I loaded the data from Excel into a QlikView table and then wanted to transpose it in the load script using the algorithm I described above. However I have a feeling I can't use the FOR....EACH logic that I've described above, it fails when I try to refresh the data. Maybe I'm using the wrong approach entirely and there's a better way to achieve this?

Thanks!

Anosh

IAMDV
Luminary Alumni
Luminary Alumni

Anosh - Please can you post the QV document with sample data? It will be easy to visualize the issue...

Thanks - DV

nagaiank
Specialist III
Specialist III

What you have is a pivot table with four dimensions (Year, User, Product, and Quarter) and one fact (Goal).

What you want also is a pivot table with the same four dimensions and one fact with the position of dimensions Product and Quarter changed.

You may load the source pivot table into a Qlikview logical table using the table load wizard, which generates a script like this one below and then generate the second pivot table or any other pivot table in the format you need.

CrossTable(Q, Data, 3)

LOAD YEAR,

     USER,

     PRODUCT,

     [GOAL Q1],

     [GOAL Q2],

     [GOAL Q3],

     [GOAL Q4]

FROM

C:\Users\Kris\Desktop\pivot.xlsx

(ooxml, embedded labels, table is Sheet1);

Hope this helps.

Not applicable
Author

Thank you krishnamorrthy! That worked wonderfully.....and I learned how useful the CROSSTABLE function can be! Much appreciated!

- Anosh