Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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

Re: Multiple header from Excel into Qlikview

I left out the excel file in my post.

Re: Multiple header from Excel into Qlikview

Perhaps this document helps: multi_header_pivot_import.qvw


talk is cheap, supply exceeds demand
Not applicable

Re: Multiple header from Excel into Qlikview

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
Valued Contributor II

Re: Multiple header from Excel into Qlikview

Hi,

We can get by using cross table function as well.

Re: Multiple header from Excel into Qlikview

See attached qvw.


talk is cheap, supply exceeds demand
Not applicable

Re: Multiple header from Excel into Qlikview

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

Re: Multiple header from Excel into Qlikview

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

Re: Multiple header from Excel into Qlikview

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

Not applicable

Re: Multiple header from Excel into Qlikview

Hi ,

  Use cross table to make Year as Column

Crosstable(Attribute,data,2)

Thanks

Manju

Community Browser