Manipulating Data / Cross Table

[#1]

Hi

I have the following data (small snapshot) that I am unable to change at the source:

PRIMARY_TICKER	LAST_YEAR	CROCI_INCL_LEASES_NOMCY 2004	CROCI_INCL_LEASES_NOMCY 2005	CROCI_INCL_LEASES_NOMCY 2006	CROCI_INCL_LEASES_NOMCY 2007	CROCI_INCL_LEASES_NOMCY 2008	CROCI_INCL_LEASES_NOMCY 2009	CROCI_INCL_LEASES_NOMCY 2010	CROCI_INCL_LEASES_NOMCY 2011	CROCI_INCL_LEASES_NOMCY 2012	CROCI_INCL_LEASES_NOMCY 2013	CROCI_INCL_LEASES_NOMCY 2014	CROCI_INCL_LEASES_NOMCY 2015	CROCI_INCL_LEASES_NOMCY 2016	CROCI_INCL_LEASES_NOMCY 2017	CROCI_INCL_LEASES_NOMCY 2018	ROE_PUBCY 2004	ROE_PUBCY 2005	ROE_PUBCY 2006	ROE_PUBCY 2007	ROE_PUBCY 2008	ROE_PUBCY 2009	ROE_PUBCY 2010	ROE_PUBCY 2011	ROE_PUBCY 2012	ROE_PUBCY 2013	ROE_PUBCY 2014	ROE_PUBCY 2015	ROE_PUBCY 2016	ROE_PUBCY 2017	ROE_PUBCY 2018
000001.SZ	2013	0.15	0.26	0.27													0.079808	0.061236	0.249077	0.273646	0.040757	0.276637	0.238087	0.17739	0.17043	0.157029	0.152267	0.154354	0.148036
000002.SZ	2013	0.152048	0.19341	0.164162	0.180302	0.099536	0.154976	0.65611	0.147461	0.175764	0.17426	0.185407	0.342444	4.924643			0.167883	0.181497	0.172767	0.19086	0.108553	0.128229	0.150111	0.147748	0.169116	0.163581	0.150569	0.157382	0.147828

I want to pull in the data into a cross tab to like this:

PRIMARY_TICKER	Metric	Year	Data
000001.SZ	CROCI_INCL_LEASES	CY 2004	0.15
000001.SZ	CROCI_INCL_LEASES	CY 2005	0.26
000001.SZ	CROCI_INCL_LEASES	CY 2006	0.27

Would also be great if I could remove the "CY " from the Year!

Many thanks

T

[Gysbert_Wassenaar · Accepted solution]

See attached qvw.

[#3]

Gysbert Wassenaar

I promise no more after this one Gysbert! !

I also want to quartile the data you have created in the attached  against another field in my model called "Conviction Sector description"

So essentially I want to quartile a group of "Primary Tickers" against a "Conviction sector" and "Year".   and join the quartile data to the same table (as below). Is this an Aggr function?

PRIMARY_TICKER	Metric	Conviction Sector description	Year	Data	Sector Quartile
000001.SZ	CROCI_INCL_LEASES	Oil	CY 2004	0.15	3
000002.SZ	CROCI_INCL_LEASES	Oil	CY 2004	0.26	2
000003.SZ	CROCI_INCL_LEASES	Oil	CY 2004	0.27	1
000003.SZ	CROCI_INCL_LEASES	Oil	CY 2004	0.01	4

I appreciate this is probably pushing it - but any help would be most grateful.

Many thanks!