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

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.

2015-08-01 14_27_52.png

I want the table in QlikView will look like this.

2015-08-01 14_25_45.png

Please tell me how this can be achieved.

Thank you in advance.

9 Replies
Not applicable
Author

I left out the excel file in my post.

Gysbert_Wassenaar

Perhaps this document helps: multi_header_pivot_import.qvw


talk is cheap, supply exceeds demand
Not applicable
Author

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))
))

vardhancse
Specialist III
Specialist III

Hi,

We can get by using cross table function as well.

Gysbert_Wassenaar

See attached qvw.


talk is cheap, supply exceeds demand
Not applicable
Author

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.

2015-08-03 01_00_34-Microsoft Excel - Sample1_shrink_columns.xlsx.png

Gysbert_Wassenaar

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
Not applicable
Author

I see now. However is there another way to format the year without listing each one individually in the Temp Year?

Not applicable
Author

Hi ,

  Use cross table to make Year as Column

Crosstable(Attribute,data,2)

Thanks

Manju