Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Help me to make this cross table script more efficient!

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]

....

1 Reply
jonbrough
Specialist
Specialist

Might be worth posting the original spreadsheet, either with the figures hacked or most of the data rows deleted.

I did a crosstable solution for a surveymonkey export that worked with mapping tables & applymap commands to get deal with complex groupings of columns and unhelpful column titles, but this may not be necessary in your case.

Jonathan