Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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
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% |