Need help in Cross Table utilization

[israrkhan]

Hi Guys,

i am trying to use cross table in a complex excel file.(for me its complex )

my excel sheet format is like below table, WITH SAMPLE DATA..

for each year i have 12 column and two extra column as Actual and Budget for that year.

Country (Dimension)	Dealer (Dimension)	Jan-2011	Feb-2011	upto Dec-2011	Actual 2011	Target 2011	Jan-2012	Feb-2012	upto Dec-2012	Actuall 2012	Target 2012	Jan-2013	Feb-1013	Mar-2013	upto Dec-2013	Actuall 2013	Target 2013
UK	AAA	500	663	56	1000	2000	355	546
USA	BBB	456	35	45	200	300	34	354
CHINA	CCC	865	676	3	300	400	56	34

Questions.

1) i want to load Country, Dealer, and Jan-2011 up to Dec-2011, and Actual 2011 and Budget-2011 as a separate column, should not be in cross table. how to tell qlikview to stop cross table on Dec-2011 column.

2) How to read Country, Dealer and then ignore all column for 2011, and start from jan- 2012 upto dec-2012 and then Actuall and Budget 2012 as a separate column. is it possible without modifying the excel document.

Your comments will be highly appreciated.

THANKS IN ADVANCE..

Khan

[jonathandienst]

Hi

Not sure that I understand you completely. For this type of data I would use something like this to load:

Sales:

LOAD

     Num(SubField(Period, ' ', -1)) As Year,

     SubField(Period, ' ', 1)) As PeriodType

;

Crosstable (Period, Sales, 2)

LOAD * From mySourceExcel.xlsx (...);

This adds a year field to the crosstable loaded results. Now you can filter using Year, but I would probably do this in the front end and not in the script.

HTH

Jonathan

[israrkhan]

Thank you for your reply.

but how to handle a situation like,

let say i have 1st column as dimension and 2nd to 14 as data(cross table) and then again 15 to 18 dimensions

so how could i tell qlikview that load as cross table up to 14, and load agian 15 to 18 as dimension.

if it is not clear kindly ask me...