Cross table manipulation

[#1]

Hi

I want to manipulate the following table into a crosstable.

PRIMARY_TICKER	SALES_BRAZILFY 2012	SALES_BRAZILFY 2013	SALES_BRAZILFY 2014
0486.HK	65	45	0
1010.SE	40	10	50

I was hoping to edit the titles so that I can pull back the extra granularity of the year. (without the "FY" even better!)

This is an example of how I was hoping to transform the data? Any ideas?

Thanks very much in advance

PRIMARY_TICKER	Region	Year	Data
0486.HK	SALES_BRAZIL	FY 2012	65
0486.HK	SALES_BRAZIL	FY 2013	45
0486.HK	SALES_BRAZIL	FY 2014	0
1010.SE	SALES_BRAZIL	FY 2012	40
1010.SE	SALES_BRAZIL	FY 2013	10
1010.SE	SALES_BRAZIL	FY 2014	50

[MarcoWedel]

or maybe better:

tabColName:
CrossTable (ColID, ColName)
LOAD RecNo() as RecNo, *
FROM [http://community.qlik.com/thread/131168] (html, codepage is 1252, no labels, table is @3)
Where RecNo()<3;

mapColName:
Mapping LOAD
  ColID,
  Concat(ColName, ':', RecNo) as ColName
Resident tabColName
Group By ColID;

DROP Table tabColName;

tabData:
CrossTable (Col, Value)
LOAD *
FROM [http://community.qlik.com/thread/131168] (html, codepage is 1252, no labels, table is @3)
Where RecNo()>3;

Left Join (tabData)
LOAD Col,
    SubField(Category, ':', 1) as Category,
    Dual(SubField(Category, ':', 2), Right(SubField(Category, ':', 2),2)) as FY;
LOAD Distinct
  Col,
  ApplyMap('mapColName', Col) as Category
Resident tabData;

RENAME Fields using mapColName;

DROP Field Col;

hope this helps

regards

Marco

[#3]

Thank you so much - that is exactly what I needed!

So to complicate things further the file actually has a few other columns (and the first row) that need to be purged.  I deleted them from the example for simplicity but when I try and replicate your code I am having issues!

Can you please help me adapt the code to purge the columns below and the first row?

(2nd-10th column "Company" name to  "TPP" need to be deleted

Thank you very much again for your help,

Tok

											30	31	32	33	34	35	36	37	38	39	40	41	42	43	44	45
Tickers	Company name	Sedol	Sector	Rating	List	Market cap (current)	Price	TP	Upside	TPP	GS EPS YTD revisions				GS EPS 12m revisions				GS EPS 1w revisions				Consensus   EPS YTD revisions		Consensus EPS 12m   revisions
											FY14	FY15	FY16	FY17	FY14	FY15	FY16	FY17	FY14	FY15	FY16	FY17	FY14	FY15	FY14	FY15
MAERSKb.CO	A.P. Moeller-Maersk	4253048	Trans	Neutral		42,382	14370.00	14500.00	1%	12 months									0.0%	0.0%	0.0%	0.0%	5.5%	-3.8%	14.1%	2.7%
A2.MI	A2a SPA	5499131	Utilities	Neutral		2,596	0.83	0.93	12%	12 months	11.5%	8.1%	4.7%	-8.8%	39.8%	66.2%			0.0%	0.0%	0.0%	0.0%	0.0%	1.7%	0.0%	1.7%
AALB.AS	Aalberts	B1W8P14	SmallMid	Buy		2,298	21.01	29.00	38%	6 months	-12.6%	-11.0%	-13.3%	-14.9%					0.0%	0.0%	0.0%	0.0%	-3.1%	4.7%	-1.9%	1.7%
ABBN.VX	ABB Ltd	7108899	Machinery	Sell		39,657	20.85	20.50	-2%	12 months	-10.2%	-17.9%	-20.5%	-16.0%	-13.1%	-22.5%			0.0%	0.0%	0.0%	0.0%	-23.7%	-17.4%	-23.7%	-17.4%