Cross table

[#1]

Hi ,

I have a table like this:

I want to fetch this data in the below mentioned format:

Is it possible to do using Cross table . If yes please let me know??

[marcus_sommer]

Have a look here: Re: Re: Extracting multiple headers from Crosstable.

- Marcus

[jonathandienst]

I your first screen shot from Excel? If yes, then it is possible, although you may need  some autofills for the cells that are merged in the title, but will not be merged when loading.

I suggest that you post a sample file containing the structure and a few rows containing the data to show what I mean in pore detail.

Jonathan

[jonathandienst]

Here is an example:

	Gauteng					KZN		WCape
	Johannesburg			Tshwane		Durban	Ulundi	Cape Town
	North	South	Central	Centurion	Other
L1	86	35	69	57	5	51	56	96
L2	100	41	62	69	73	47	98	34
L3	62	27	65	15	34	23	15	26

Load this like the following:

//Create a mapping table of the composite, multiline headers (note the transpose):

MapRegions:

Mapping

LOAD Chr(Ord('A') + RowNo()) As Col,

  @1 & '|' & @2 & '|' & @3 As Label

FROM

Book1.xlsx

(ooxml, no labels, table is Sheet1, filters(

  Transpose(),

  Replace(1, top, StrCnd(null)),

  Replace(2, top, StrCnd(null))

))

Where Len(@1 & '|' & @2 & '|' & @3) > 2

;

//Load the data without headers

Temp:

LOAD A As LineID,

     B,

     C,

     D,

     E,

     F,

     G,

     H,

     I

FROM

Book1.xlsx

(ooxml, no labels, header is 3 lines, table is Sheet1);

//Now apply the header map to rename the columns:

RENAME Fields using MapRegions;

//Perform the cross table

Temp2:

CrossTable(TRegion, Value)

LOAD * Resident Temp;

//Unpack the composite headers in the final result:

Result:

LOAD SubField(TRegion, '|', 1) As Province,

  SubField(TRegion, '|', 2) As City,

  SubField(TRegion, '|', 3) As Zone,

  *

Resident Temp2;

//Clean up

Drop Tables Temp, Temp2;

Drop Field TRegion;

(sorry I cannot upload files from here due to client security policy).

HTH

Jonathan

[Anonymous]

If you have data in excel then you can get the required table format using QlikView.

Follow the below steps

QlikView --> Go to Edit Script  --> Table Files --> Select your excel file

It opens the File Wizard, Now click on next ---> Click on Enable Transformation Step

--> Next --> Click on Cross Table inside prefixes --> Now change the parameter values.

You will get the required output.

[#6]

Hi Jonathan,

Thanks for you reply can you please show me for the attached excel.I did not understand the explanation from Temp2 tables.

I have attached the excel. can you please send me the  qvd for better understanding.