Help me to make this cross table script more efficient!

Sun, 30 Dec 2012 18:55:53 GMT

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]

....

Re: Help me to make this cross table script more efficient!

Anonymous — Sun, 30 Dec 2012 23:19:48 GMT

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

topic Re: Help me to make this cross table script more efficient! in QlikView

Help me to make this cross table script more efficient!

Re: Help me to make this cross table script more efficient!