Discussion board where members can get started with QlikView.
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??
Have a look here: Re: Re: Extracting multiple headers from Crosstable.
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.
Here is an example:
Load this like the following:
//Create a mapping table of the composite, multiline headers (note the transpose):
LOAD Chr(Ord('A') + RowNo()) As Col,
@1 & '|' & @2 & '|' & @3 As Label
(ooxml, no labels, table is Sheet1, filters(
Replace(1, top, StrCnd(null)),
Replace(2, top, StrCnd(null))
Where Len(@1 & '|' & @2 & '|' & @3) > 2
//Load the data without headers
LOAD A As LineID,
(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
LOAD * Resident Temp;
//Unpack the composite headers in the final result:
LOAD SubField(TRegion, '|', 1) As Province,
SubField(TRegion, '|', 2) As City,
SubField(TRegion, '|', 3) As Zone,
Drop Tables Temp, Temp2;
Drop Field TRegion;
(sorry I cannot upload files from here due to client security policy).
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.
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.