After much browsing, I'm unable to find an elegant solution to loading a crosstable when you have multiple headers. Here is my current solution, based on a week of playing around with Qlikview (super newbie alert). Please let me know if there is a more efficient way to do this. Because I have so many records, loading the file multiple times isn't really helping performance wise. Not to mention all of the "syn" issues this creates. I end up with 5 different kinds of synids:
$Syn 1 = L10 Managed Segment+Service Order ID
$Syn 2 = L10 Managed Segment+Service Order ID+Year
$Syn 3 = L10 Managed Segment+Service Order ID+Month
$Syn 4 = $Syn 1+$Syn 3
$Syn 5 = $Syn 1+$Syn 2
Here is my load script (condensed for readability):
CrossTable(Month, [Monthly Volume], 2) //loads monthly volumes by Customer / Service ID
LOAD
[L10 Managed Segment],
[Service Order ID],
[Jan 2012 Act Vol] as [Jan 2012],
....
[Dec 2012 Act Vol] as [Dec 2012],
CrossTable(Month, [Monthly Charge], 2) //loads monthly charges by Customer / Service ID
LOAD
[L10 Managed Segment],
[Service Order ID],
[Jan 2012 Act Charges] as [Jan 2012],
....
[Dec 2012 Act Charges] as [Dec 2012],
...
CrossTable(Year, [Annual Volume], 2) //loads annual volumes by Customer / Service ID
LOAD
[L10 Managed Segment],
[Service Order ID],
[2012 FY Fcst Volumes] as 2012
[2013 FY Plan Volumes] as 2013
....
CrossTable(Year, [Annual Charges], 2) //loads annual charges by Customer / Service ID
LOAD
[L10 Managed Segment],
[Service Order ID],
[2012 FY Fcst Charges] as 2012
[2013 FY Plan Charges] as 2013
....
LOAD [Service Order ID], //Service hierarchy
[Product Category 1],
[Product Category 2],
[Product Category 3]
...
LOAD [L2 Managed Segment], //Customer hierarchy
[L3 Managed Segment],
[L4 Managed Segment],
[L5 Managed Segment],
[L6 Managed Segment],
[L7 Managed Segment],
[L8 Managed Segment],
[L9 Managed Segment],
[L10 Managed Segment]
....
