Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Cross table

Hi ,

I have a table like this:

Cross table.PNG

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

Required format.PNG

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

5 Replies
MVP & Luminary
MVP & Luminary

Re: Cross table

MVP
MVP

Re: Cross table

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
MVP
MVP

Re: Cross table

Here is an example:

 

GautengKZNWCape
JohannesburgTshwaneDurbanUlundiCape Town
NorthSouthCentralCenturionOther
L1863569575515696
L210041626973479834
L36227651534231526

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
kanchangupta
New Contributor

Re: Cross table

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.

Not applicable

Re: Cross table

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.