topic How create Pivot table for this sample file in QlikView

How create Pivot table for this sample file

Wed, 30 Mar 2011 10:43:43 GMT

I want to create pivot table based on below sample file

please explain with example.

How create Pivot table for this sample file

Thu, 31 Mar 2011 11:13:04 GMT

I basically added in this their are columns like this i want report like this.....

Week ending repair case Week ending avg subs

Market 19-feb 26-feb 5-mar 12-mar 19-mar 19-feb 26-feb 5-mar 12-mar 19-mar

A 12 7 10 8 10 182 185 192 201 204

How create Pivot table for this sample file

Thu, 31 Mar 2011 11:35:30 GMT

Hi Anand,

Looks like you need to split your crosstable function, something along the lines of (also use the Header offset in the Data load UI to get rid of the first row)...
Table1:
LOAD
B
From PivotTable.xls;

RepairCase_Temp:
crosstable(WeekendRepairCase, Data, 1)
Load
Market,
@2 as 19-Feb,
@3 as 26-Feb,
@4 as 03-Mar,
@5 as 12-Mar,
@6 as 19-Mar
From PivotTable.xls;

left join(Table1)
LOAD
Resident RepairCase_Temp;

AvgSubs_Temp:
crosstable(WeekendAvgSubs, Data, 1)
Load
Market,
@2 as 19-Feb,
@3 as 26-Feb,
@4 as 03-Mar,
@5 as 12-Mar,
@6 as 19-Mar
From PivotTable.xls;

left join(Table1)
LOAD
Resident AvgSubs_Temp;

The problem is your columns names are the same, that's why you need to use the @ to reference the column number. You can use the crosstable function multiple times, with a left join. All you need to do is load straight columns in one load statment, use crosstable function on one part of the source file, left join to the previous table, use crosstable on next part, left join, so on and so forth.

If you keep adding columns as the months go by, you will need a more "intelligent" (automated) piece of script. Can't help you on that, it would take more time than I want to spend answer this thread.

Parameters for crosstable function are as follows:

crosstable(X, Y, Z), where X is the fieldname you want to give to the column headers you are unpivoting, Y is the fieldname you want to give of the data part of the pivoted part of the table, and Z is the number of qualified fields (i.e. the number of columns you are loading in that statement which are already in column format, i.e. typically the row header already contains the fieldname, not a field value).

Hope this helps.

How create Pivot table for this sample file

Thu, 31 Mar 2011 11:38:36 GMT

Sorry, slight typo, script must read:

Table1:
LOAD
Market
From PivotTable.xls;

RepairCase_Temp:
crosstable(WeekendRepairCase, Data, 1)
Load
Market,
@2 as 19-Feb,
@3 as 26-Feb,
@4 as 03-Mar,
@5 as 12-Mar,
@6 as 19-Mar
From PivotTable.xls;

left join(Table1)
LOAD
Resident RepairCase_Temp;

AvgSubs_Temp:
crosstable(WeekendAvgSubs, Data, 1)
Load
Market,
@2 as 19-Feb,
@3 as 26-Feb,
@4 as 03-Mar,
@5 as 12-Mar,
@6 as 19-Mar
From PivotTable.xls;

left join(Table1)
LOAD
Resident AvgSubs_Temp;

If you load the pivot table from a data source, you can use the UI (with associated colour coding for building the crosstable function) via the CrossTable... button.

How create Pivot table for this sample file

Thu, 31 Mar 2011 12:39:51 GMT

Week ending repair case Week ending avg subs

Market 19-feb 26-feb 5-mar 12-mar 19-mar 19-feb 26-feb 5-mar 12-mar 19-mar

A 12 7 10 8 10 182 185 192 201 204

Good explain but data is come from this columns like:-

Market, Created date, Case Number, Customer No

A 1/2/2011 6:22 00228402 6000036134

B 1/2/2011 6:48 00228407 6000037219

C 1/2/2011 7:36 00228413 6000032951

D 1/2/2011 7:43 00228416 6000030740

E 1/2/2011 7:54 00228422 6000037378

I need to show "Week ending repair case" repair cases "Week ending avg subs"

Please explain this example in *.QVW file.