topic Re: Manipulating Data / Cross Table in QlikView

Manipulating Data / Cross Table

Thu, 06 Nov 2014 18:35:49 GMT

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

Re: Manipulating Data / Cross Table

Gysbert_Wassenaar — Thu, 06 Nov 2014 18:46:29 GMT

See attached qvw.

Re: Manipulating Data / Cross Table

Thu, 06 Nov 2014 19:30:11 GMT

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!