Hi , i am new in qv. i have one issue when i am importing pivot formated excel in qv. After importing excel if i select category AA and Subcategory c then it showing only Apr'14,Aug'14and Oct'14 and H1 2014-15 and Q2 14-15 data but others are not showing. and values are showing wrong. Please help . a sample data i am using this script:
// If I did things correctly the only thing you have to do is enter the field names
// of your horizontal and vertical dimensions in the tables HDims and VDims in
// the correct order and set the variables vSourceFile, vSheet and vType to the
// correct values for your source excel file.
SET vSourceFile = 'Book12.xlsx'; // 'pivotimporttest2.xlsx'
SET vSheet = '[Sheet1]'; // '[Sheet1]'
SET vType = 'ooxml'; // 'ooxml'
// Put the field names of your horizontal dimensions in the HDims table
// NOTE: Qlikview is case-sensitive, so make sure you don't forget any capitals
// in your field names.
HDims:
load * inline [
HDimLevel, HFieldName
HDim1, Account
HDim2, Acc1
HDim3, Year
HDim4, MonthYear
];
// Put the field names of your column dimensions in the VDims table
// NOTE: Qlikview is case-sensitive, so make sure you don't forget any capitals
// in your field names.
VDims:
load * inline [
VFieldName
Category
Subcategory
];
// Get the number of horizontal and vertical dimensions
LET vHDims = FieldValueCount('HFieldName');
LET vVDims = FieldValueCount('VFieldName');
// Create vHFieldList as the list of fields of the horizontal dimensions to load
Temp1:
load concat('@' & RecNo() & ' as ' & HDimLevel ,',') as HFieldList
Resident HDims;
LET vHFieldList = peek('HFieldList');
// Create helper variables for the transformation of the pivot used to create the Levels table
SET vRemoveRows = Remove(Row, Pos(Top, 1));
SET vReplaces = 'Replace(1, top, StrCnd(null))';
for i = 1 to $(vVDims) -1
SET vRemoveRows =$(vRemoveRows),Remove(Row, Pos(Top, 1));
next i
for i = 1 to $(vHDims) -1
SET vReplaces = $(vReplaces),Replace($(i), top, StrCnd(null));
next i
Drop table Temp1;
// Pivot the horizontal dimensions to vertical using the transpose function
// and add a ColNo field so the table can later be associated with the Results table
Levels:
LOAD $(vHFieldList),
rowno()+$(vVDims) as ColNo
FROM $(vSourceFile)
($(vType), no labels, table is $(vSheet), filters(
Transpose(),
$(vRemoveRows),
$(vReplaces)
));
// Load the pivot table with the Crosstable function
LET vHeaders = $(vHDims)-1;
CT:
CrossTable(ValCol, Amount,$(vVDims))
load * from $(vSourceFile)
($(vType), embedded labels, header is $(vHeaders) lines, table is $(vSheet));
// Create vVFieldList to use for comparisons in the creation of the ColNo field
Temp2:
load concat('['&VFieldName&']','&') as VFieldList resident VDims;
LET vVFieldList = peek('VFieldList');
Drop Table Temp2, VDims;
// Add the ColNo field so the table can be associated with the Levels table
ResultTable:
NoConcatenate
load *,
autonumber(RecNo(),$(vVFieldList)) + $(vVDims) as ColNo
Resident CT;
join load * Resident Levels;
drop table Levels;
// Clean up the intermediate CT table
drop Table CT;
drop field ValCol;
// Rename the Levels fields to their proper names
HFieldMap: mapping load * resident HDims;
RENAME Fields using HFieldMap;
Drop Table HDims;
