Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Manipulating Data / Cross Table

Hi

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

PRIMARY_TICKER

LAST_YEARCROCI_INCL_LEASES_NOMCY 2004CROCI_INCL_LEASES_NOMCY 2005CROCI_INCL_LEASES_NOMCY 2006CROCI_INCL_LEASES_NOMCY 2007CROCI_INCL_LEASES_NOMCY 2008CROCI_INCL_LEASES_NOMCY 2009CROCI_INCL_LEASES_NOMCY 2010CROCI_INCL_LEASES_NOMCY 2011CROCI_INCL_LEASES_NOMCY 2012CROCI_INCL_LEASES_NOMCY 2013CROCI_INCL_LEASES_NOMCY 2014CROCI_INCL_LEASES_NOMCY 2015CROCI_INCL_LEASES_NOMCY 2016CROCI_INCL_LEASES_NOMCY 2017CROCI_INCL_LEASES_NOMCY 2018ROE_PUBCY 2004ROE_PUBCY 2005ROE_PUBCY 2006ROE_PUBCY 2007ROE_PUBCY 2008ROE_PUBCY 2009ROE_PUBCY 2010ROE_PUBCY 2011ROE_PUBCY 2012ROE_PUBCY 2013ROE_PUBCY 2014ROE_PUBCY 2015ROE_PUBCY 2016ROE_PUBCY 2017ROE_PUBCY 2018
000001.SZ20130.150.260.270.0798080.0612360.2490770.2736460.0407570.2766370.2380870.177390.170430.1570290.1522670.1543540.148036
000002.SZ20130.1520480.193410.1641620.1803020.0995360.1549760.656110.1474610.1757640.174260.1854070.3424444.9246430.1678830.1814970.1727670.190860.1085530.1282290.1501110.1477480.1691160.1635810.1505690.1573820.147828

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

PRIMARY_TICKERMetricYearData
000001.SZCROCI_INCL_LEASESCY 20040.15
000001.SZCROCI_INCL_LEASESCY 20050.26
000001.SZCROCI_INCL_LEASESCY 20060.27

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

Many thanks

T

1 Solution

Accepted Solutions

Re: Manipulating Data / Cross Table

See attached qvw.


talk is cheap, supply exceeds demand
2 Replies

Re: Manipulating Data / Cross Table

See attached qvw.


talk is cheap, supply exceeds demand
Not applicable

Re: Manipulating Data / Cross Table

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_TICKERMetricConviction Sector descriptionYearDataSector Quartile
000001.SZCROCI_INCL_LEASESOilCY 20040.153
000002.SZCROCI_INCL_LEASESOilCY 20040.262
000003.SZCROCI_INCL_LEASESOilCY 20040.271
000003.SZCROCI_INCL_LEASESOilCY 20040.014

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

Many thanks!

Community Browser