topic Re: Need help in Cross Table utilization in QlikView

Need help in Cross Table utilization

israrkhan — Thu, 12 Dec 2013 09:34:05 GMT

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
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

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

AAA

500

663

1000

2000

355

546

USA

BBB

456

200

300

354

CHINA

CCC

865

676

300

400

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

Re: Need help in Cross Table utilization

jonathandienst — Thu, 12 Dec 2013 09:54:50 GMT

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

Re: Need help in Cross Table utilization

israrkhan — Thu, 12 Dec 2013 12:18:26 GMT

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...