- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Multiple header from Excel into Qlikview
Hello,
I have an excel file where the first Row of the he ! !ader contains the years 2014 and 2015, and right underneath the Years are columns for the Months. I want the Years to somehow to be treated as a column and link to the respective Month.
I want the table in QlikView will look like this.
Please tell me how this can be achieved.
Thank you in advance.
- Tags:
- qlikview_scripting
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I left out the excel file in my post.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Perhaps this document helps: multi_header_pivot_import.qvw
talk is cheap, supply exceeds demand
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you for the help! gwassenaar However, I tried the method and got this error. Do you know what may be the problem?
Syntax error, missing/misplaced FROM:
Levels:
LOAD @1 as HDim1,@2 as HDim2,
rowno()+ as ColNo
FROM C:\Users\will35\Desktop\Qlikview Issues\Sample File\Sample1.xlsx
(biff, no labels, table is [Sheet1], filters(
Transpose(),
Remove(Row, Pos(Top, 1)),
Replace(1, top, StrCnd(null)),Replace(1, top, StrCnd(null))
))
Levels:
LOAD @1 as HDim1,@2 as HDim2,
rowno()+ as ColNo
FROM C:\Users\will35\Desktop\Qlikview Issues\Sample File\Sample1.xlsx
(biff, no labels, table is [Sheet1], filters(
Transpose(),
Remove(Row, Pos(Top, 1)),
Replace(1, top, StrCnd(null)),Replace(1, top, StrCnd(null))
))
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
We can get by using cross table function as well.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
See attached qvw.
talk is cheap, supply exceeds demand
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you again gwassenaar that worked!
I see that you used TempYear to bring in the year. So any new year that is added to the excel file will need to be added to this code later. Is there a code that can detect that any 12 merged cells in the first row contains the year? Another way that could work is to declare infinite years in TempYear.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I used TempYear only to adjust the number formatting of the year. It has no effect on which years are loaded from the excel file. You can simply leave out that table and see what happens.
talk is cheap, supply exceeds demand
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I see now. However is there another way to format the year without listing each one individually in the Temp Year?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi ,
Use cross table to make Year as Column
Crosstable(Attribute,data,2)
Thanks
Manju