topic Manipulating a table in QlikView

Manipulating a table

Tue, 26 Jan 2010 16:17:48 GMT

I am new to Qlikview 9:

I have a excel spreadsheet that looks like:

	275	276	277	279	280	282	284	286	288	289	290	Website
7/1/2006	200,000.00	850,000.00	400,000.00	550,000.00	700,000.00	700,000.00						1,500,000.00
10/1/2006	190,000.00	808,000.00	380,000.00	525,000.00	665,000.00	665,000.00						1,425,000.00
1/1/2007	190,000.00	808,000.00	380,000.00	525,000.00	665,000.00	665,000.00						1,425,000.00
4/1/2007	220,000.00	935,000.00	440,000.00	605,000.00	770,000.00	770,000.00						1,650,000.00
7/1/2007	580,000.00	1,600,000.00	775,000.00	630,000.00	805,000.00	450,000.00				1,250,000.00	350,000.00	1,500,000.00
10/1/2007	550,000.00	1,520,000.00	735,000.00	600,000.00	765,000.00	425,000.00	665,000.00			1,175,000.00	330,000.00	1,425,000.00
1/1/2008	550,000.00	1,520,000.00	735,000.00	600,000.00		425,000.00	665,000.00			1,175,000.00	330,000.00	1,425,000.00
4/1/2008	638,000.00	1,750,000.00	853,000.00	693,000.00		495,000.00	770,000.00			1,375,000.00	385,000.00	1,650,000.00
7/1/2008	667,000.00	1,850,000.00	850,000.00	725,000.00		660,000.00	1,400,000.00	450,000.00	450,000.00	1,400,000.00	725,000.00	2,750,000.00
10/1/2008	635,000.00	1,750,000.00	800,000.00	680,000.00		627,000.00	1,330,000.00	425,000.00	425,000.00	1,330,000.00	685,000.00	3,450,000.00
1/1/2009	635,000.00	1,750,000.00	800,000.00	680,000.00		627,000.00	1,330,000.00	425,000.00	425,000.00	1,330,000.00	685,000.00	4,300,000.00
4/1/2009	735,000.00	2,035,000.00	935,000.00	795,000.00		726,000.00	1,540,000.00	495,000.00	495,000.00	1,540,000.00	795,000.00	5,375,000.00
7/1/2009	700,000.00	1,940,000.00	890,000.00	725,000.00		693,000.00	1,470,000.00	470,000.00	470,000.00	1,470,000.00	760,000.00	5,900,000.00

I am seeing all these options like Transpose and Rotate in version9, I am not sure how to use them to get the data I need. I need 3 dimensions

SalesPersonID (which is the numbers up top), Date, and SalesQuota…. i.e. (275, 7/1/2006, $200,000 as one record)

I am unsure how to manipulate this data to get this set up, any help?

Manipulating a table

Tue, 26 Jan 2010 17:15:02 GMT

Hello !

I think you need to use CROSSTABLE on your LOAD.

The first row of your data would be transformed into 6 records.

Take a look at the CROSSTABLE docs (related to LOAD) and if you are unuble to use it, let us know.

It would be HELPFUL if you provided a SIMPLE data base or QVW here, with INLINE data, where we could help you, OK ?

Here's how I use the CROSSTABLE on a particular situation of ours :


 CROSSTABLE (CODIGO_PRE_FPMOV,VALOR_FPMOV,7) LOAD
 YEAR(MAKEDATE(LEFT(AAAAMM_FPMVA,4),RIGHT(AAAAMM_FPMVA,2))) AS ANO_DER_FPMOV
 ,MONTH(MAKEDATE(LEFT(AAAAMM_FPMVA,4),RIGHT(AAAAMM_FPMVA,2))) AS MES_DER_FPMOV
 ,AAAAMM_FPMVA AS AAAAMM_FPMOV
 ,PRONTU_FPMVA AS PRONTU_FPMOV
 ,DEPART_FPMVA AS DEPART_FPMOV
 ,'ADM' AS FOLHA_FPMOV
 ,'PIRACICABA/SBO' AS CAMPUS_FPMOV
 ,VENDES_1_FPMVA
 ,VENDES_2_FPMVA
 ,VENDES_3_FPMVA
VENDES_4_FPMVA

See ya !

Manipulating a table

Tue, 26 Jan 2010 17:44:00 GMT

Hey !

Guess what ? I have a sample here, based on your data.

Try the attached file. Study it.

Key point is this line :

CROSSTABLE (CODE_NO,CODE_VALUE,1) LOAD *

Hope you will understand !

By the way, it is ALWAYS a good idea to provide those simple QVWs as I did here, when you post. It helps other fellows to help you.

I also keep a bunch of those tiny QVWs , each one with a specific situation. Then I test the issue OUTSIDE the original problem. When I have the solution, I take it to the REAL world, wich generally is more complex. Got it ?

See ya !