Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
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