Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Specialist II
Specialist II

how to deal with two level of header in excel

Hi Community,

i have a excel file which contains Two Level of header,

i wana know, what will be the best approach to load this type of data..

my excel format is like below:

Header 1Header 2Header 3Header 4Header 5Header 6Header 7Header 8Header 9Header 10Header 11Header 12Header 13Header 14Header 15Header 16
Current MonthPrev MonthYTD
NameNumberAccountIDSaleSale %EstimatedActualSaleSale %EstimatedActualSaleSale %EstimatedActual

Your Comment would be highly Appreciated.

Many Thanks

Khan

8 Replies
Highlighted
Specialist II
Specialist II

Re: how to deal with two level of header in excel

no one have an idea???

Highlighted
MVP
MVP

Re: how to deal with two level of header in excel

Highlighted
Creator
Creator

Re: how to deal with two level of header in excel

Hi,

Hope this will help you, PFA , sample app and XL

you need to load twice for Current Month and Previous Month ,Concatenate the 2 tables and an identifier

field (Current Month and Previous Month)

Steps for Loading

CurrentMonthLoad:

1)  In File Wizard type - select Embedded Lables and header size - > Lines -> 2 and click on next

2)In Garbage Tab select conditional Delete and select the Previous column sale, sale % , estimated and actual and click on Delete Marked , Click on Next and Finish

3) Identifier Field 'CurrentMonth' as Monthstatus

4) Concatenate

Previous MonthLoad:

5) In File Wizard type - select Embedded Lables and header size - > Lines -> 2 and click on next

6)in Garbage Tab select conditional Delete and select the Current column sale, sale % , estimated and actual and click on Delete Marked , Click on Next and Finish

7) Rename  Field like Sale1 to Sale, Sale1% To Sale1, Estimated1 to Esitmated, Actual1 to Actual

7) Identifier Field  'PreviousMonth' as Monthstatus !


Note : If your Data volume is high , this loading technique may affect reloading time , since we are loading twice



Highlighted
MVP & Luminary
MVP & Luminary

Re: how to deal with two level of header in excel

Hi,

Check this link by Gysbert.

multi_header_pivot_import.qvw

Highlighted
Specialist II
Specialist II

Re: how to deal with two level of header in excel

it has many error guys?

Highlighted
MVP
MVP

Re: how to deal with two level of header in excel

If you think that red lines are errors, please ignore them... it will work.. try to read the script and implement the same for your file.

Highlighted
MVP & Luminary
MVP & Luminary

Re: how to deal with two level of header in excel

Hi,

If you attach your Excel template then it would be easier to provide solution.

Regards,

jagan.

Highlighted
Specialist
Specialist

Re: how to deal with two level of header in excel

Hi Shanmuga Prabhu

Actually I have had read multiples xls from 1 folder. Problem is 4 of the all headers are 1 row upper and their place there are numbers. Those numbers goes chanching according to excel file. I Ittached your excel file but with problem sheet. How could read this situation in qlikview?

Thanks!