Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
I left out the excel file in my post.
Perhaps this document helps: multi_header_pivot_import.qvw
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))
))
Hi,
We can get by using cross table function as well.
See attached qvw.
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.
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.
I see now. However is there another way to format the year without listing each one individually in the Temp Year?
Hi ,
Use cross table to make Year as Column
Crosstable(Attribute,data,2)
Thanks
Manju