Determine the values [Cross Table]

[#1]

*Edited*

Hello and Good Day!

Since I am new at using qlikview, I find it hard to analyze what should be my steps when cleaning an excel file. I have

an idea of using crosstables when encountering this kinds of problems.

attached file is the excel file.

What I need:

1. To know if it is under what year.

2. To know if it is under Revenue or Visitors

3. To know what is public or private

Thank you.

[Gysbert_Wassenaar]

See attached files. This is rather convoluted. Perhaps somebody has a more efficient solution. However, I think fixing your excel sheet so it's more easily imported is a better solution. I hope this helps.

[#3]

based on the excel file, they are categorized based on revenue and visitors, and it depends on the year.

so based on the sample excel file I have given, those two category Revenue and Visitors is under for example September 2011, another one is under Semptember 2012 but under the same category [Revenue and Visitors]

[#4]

thank you

I will try your solution!

[#5]

bump. I have updated my post. Please see attached file.

[#6]

Did you ever find a simpler solution to loading multiple headers into a crosstable? I don't really understand what your script is doing...

[#7]

Gysbert says it's convoluted.  But I think Gysbert's load script just manually provides the "members" of each Dimension -- hard-coded like "Public" / "Private" , "Revenue" / "Visitors". The challenge for me is there is it can't "scale" to arbitrary number of Members and Dimensions unless we hard-code it.

So I think Gysbert is correct -- best thing is to "fix the Excel sheet"

Here is a question on SuperUser who wants to do the same, that is "unpivot" a 1-Dimension Excel file.

Is it possible to "unpivot" or "reverse pivot" in Excel? - Super User

I asked in the comments how to do 2-Dimensions Excel file, or arbitrary number of Dimensions, which I think is possible

http://superuser.com/questions/78439/is-it-possible-to-unpivot-or-reverse-pivot-in-excel/78464#comme...

Wonder if anyone has advice on "fixing the Excel sheet"