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

[jerem1234 · Accepted solution]

I used this bit of code:

Table1:

Load * Inline [

PRIMARY_TICKER,SALES_BRAZILFY 2012,SALES_BRAZILFY 2013,SALES_BRAZILFY 2014

0486.HK,65,45,0

1010.SE,40,10,50

];

Table2:

CrossTable('Region', 'Data')

Load *

Resident Table1;

FINAL:

Load PRIMARY_TICKER,

left(subfield(Region, ' ', 1), len(subfield(Region, ' ', 1))-2) as Region,

//'SALES_BRAZIL' as Region (if you can hard-code it)

subfield(Region, ' ', 2) as Year,

Data

Resident Table2;

Drop Table Table1, Table2;

Of course, the inline table first was for me just to get the data in. Please find attached as well.

Hope this helps!

[#3]

Thank you very much!

[jerem1234]

If your question has been answered, please close the thread by marking helpful/correct.

Thanks!!

[MarcoWedel]

Hi,

another one:

tabSales:
CrossTable (RegYear, Data)
LOAD * FROM [http://community.qlik.com/thread/131168] (html, codepage is 1252, embedded labels, table is @1);

Left Join (tabSales)
LOAD Distinct
    RegYear,
  SubField(RegYear, 'FY', 1) as Region,
  Right(RegYear,4) as Year
Resident tabSales;

hope this helps

regards

Marco

[#6]

I have another very similar Issue...I am trying to load the following as a cross table. It doesn't seem to recognize the wrapped cells.

I appreciate your help! Thanks

Tickers	GS EPS YTD revisions				GS EPS 12m revisions
	FY14	FY15	FY16	FY17	FY14	FY15	FY16	FY17
M.L	2.3%	0.5%	34.1%	14.1%	247.1%	848.7%	1360.9%	506.2%
GG.L	-89.2%	-300.8%	-60.6%	33.7%	-91.7%	-2290.9%	-526.7%	-168.9%

[jerem1234]

How are you trying to load it in? I can see that being a problem since you have two headers. You need one headers with the fields having unique names like YTD_FY14, YTD_FY15, ...., 12m_FY14, FY15_12m, ...

Then load them in and do a crosstable.

Looks like you'll need to update the excel file format

Hope this helps!

[#8]

Unfortunately I can not edit the source data... so it is not possible?

[jerem1234]

It is possible, but you'll have to do a little more work in script. You'll have to load it in without the field names, then assign them yourself with code like:

Table1:

LOAD A as Tickers,

    B as YTD_FY14,

    C as YTD_FY15,

    D as YTD_FY16,

    E as YTD_FY17,

    F as TwelveM_FY14,

    G as TwelveM_FY15,

    H as TwelveM_FY16,

    I as TwelveM_FY17

FROM

Data.xlsx

(ooxml, no labels, header is 2 lines, table is Sheet1);

CrossTable('Year', 'Data')

Table2:

Load * Resident Table1;

Drop Table Table1;

Also if you can't get it by using this code as an example, use the transformation/file wizard by clicking on Table Files:

Hope this helps!

[MarcoWedel]

Hi again,

let me try one possible solution:

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 Distinct
  Col,
  ApplyMap('mapColName', Col) as Category
Resident tabData;

RENAME Fields using mapColName;

DROP Field Col;

hope this helps

regards

Marco