topic Re: Multiple header from Excel into Qlikview in QlikView

Multiple header from Excel into Qlikview

Sat, 01 Aug 2015 21:47:07 GMT

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.

Re: Multiple header from Excel into Qlikview

Sat, 01 Aug 2015 21:48:50 GMT

I left out the excel file in my post.

Re: Multiple header from Excel into Qlikview

Gysbert_Wassenaar — Sun, 02 Aug 2015 15:41:29 GMT

Perhaps this document helps: multi_header_pivot_import.qvw

Re: Multiple header from Excel into Qlikview

Mon, 03 Aug 2015 03:43:15 GMT

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

Re: Multiple header from Excel into Qlikview

vardhancse — Mon, 03 Aug 2015 05:10:54 GMT

Hi,

We can get by using cross table function as well.

Re: Multiple header from Excel into Qlikview

Gysbert_Wassenaar — Mon, 03 Aug 2015 05:56:12 GMT

See attached qvw.

Re: Multiple header from Excel into Qlikview

Mon, 03 Aug 2015 08:05:26 GMT

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.

Re: Multiple header from Excel into Qlikview

Gysbert_Wassenaar — Mon, 03 Aug 2015 09:03:07 GMT

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.

Re: Multiple header from Excel into Qlikview

Mon, 03 Aug 2015 09:54:40 GMT

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

Re: Multiple header from Excel into Qlikview

Mon, 03 Aug 2015 10:14:40 GMT

Hi ,

Use cross table to make Year as Column

Crosstable(Attribute,data,2)

Thanks

Manju