Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
Anosh - Have you considered transposing the data in QV Load Script?
Thanks - DV
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
Anosh - Please can you post the QV document with sample data? It will be easy to visualize the issue...
Thanks - DV
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.
Thank you krishnamorrthy! That worked wonderfully.....and I learned how useful the CROSSTABLE function can be! Much appreciated!
- Anosh