topic Re: How to pull data from crosstable in QlikView

How to pull data from crosstable

Tue, 17 Jan 2012 12:07:15 GMT

Hi everyone,

I am facing a problem while pulling data from a crosstable. Below table displays maintenance cost for different products per year for Q1 to Q4. This table is present in excel sheet.

	2010				2011
Product	Q1	Q2	Q3	Q4	Q1	Q2	Q3	Q4
Cycle	1000	6500	4560	5860	2000	4500	1230	2450
Bike	2000	4500	1230	2450	1000	6500	4560	5860
Car	3000	5000	3210	6580	2000	3000	5000	3210
Truck	4000	500	4560	1245	3000	4000	500	4560

I want this data to be pulled in a QVW and dispalyed in below manner. How to pull data from above kind of tables in a organised way?

Product	Year	Quarter	Maintenance Cost
Cycle	2007	Q1	1000
Cycle	2007	Q2	6500
Cycle	2007	Q3	4560
Cycle	2007	Q4	5860
Bike	2007	Q1	2000
Bike	2007	Q2	4500
Bike	2007	Q3	1230
Bike	2007	Q4	2450

Thanks,

Amey.

Re: How to pull data from crosstable

lironbaram — Tue, 17 Jan 2012 12:30:25 GMT

hei

attach is an example may be you can do somthing similar

what the load script does is rotate the table and then cross table it

Re: How to pull data from crosstable

Wed, 18 Jan 2012 08:19:49 GMT

Hi Liron,

Thanks a lot it works .

Re: How to pull data from crosstable

sridhar240784 — Wed, 18 Jan 2012 08:45:24 GMT

Hi,

if i understood your question,this could be done with QlikView ETL Transform functionality.

Target your file and Say Next to Enabel the Transformation Steps. Then you can play with Transpose and Fill Value in Wizard.

Have a look at the attached application.

Hope this helps you.

-Sridhar

Re: How to pull data from crosstable

Thu, 19 Jan 2012 14:51:11 GMT

Hi Shridhar,Yes we can achieve it by this method also. But I have a doubt, whether this method will remain useful even for large and uneven cross tables???