topic Iterate or Loop through records in a QlikView table in QlikView

Iterate or Loop through records in a QlikView table

Thu, 12 May 2011 16:01:24 GMT

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	USER	PRODUCT	GOAL Q1	GOAL Q2	GOAL Q3	GOAL Q4
2011	John Doe	Widget 1	100	200	150	250
2010	John Doe	Widget 2	50	75	85	120
2011	Tom Jones	Widget 1	20	30	35	40
2011	Tom Jones	Widget 2	5	6	7	8

I want to transpose the data into the following format:

YEAR	USER	QUARTER	WIDGET 1 GOAL	WIDGET 2 GOAL
2010	John Doe	Q1	0	50
2010	John Doe	Q2	0	75
2010	John Doe	Q3	0	85
2010	John Doe	Q4	0	120
2011	John Doe	Q1	100	0
2011	John Doe	Q2	200	0
2011	John Doe	Q3	150	0
2011	John Doe	Q4	250	0
2011	Tom Jones	Q1	20	5
2011	Tom Jones	Q2	30	6
2011	Tom Jones	Q3	35	7
2011	Tom Jones	Q4	40	8

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

Iterate or Loop through records in a QlikView table

IAMDV — Thu, 12 May 2011 16:11:29 GMT

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

Thanks - DV

Iterate or Loop through records in a QlikView table

Thu, 12 May 2011 16:14:26 GMT

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

Iterate or Loop through records in a QlikView table

IAMDV — Thu, 12 May 2011 16:20:35 GMT

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

Thanks - DV

Iterate or Loop through records in a QlikView table

nagaiank — Thu, 12 May 2011 16:54:06 GMT

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.

Iterate or Loop through records in a QlikView table

Mon, 16 May 2011 16:26:49 GMT

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

- Anosh