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

[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

[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

[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