topic Re: Cross table manipulation in QlikView

Cross table manipulation

Thu, 28 Aug 2014 16:33:17 GMT

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

Re: Cross table manipulation

jerem1234 — Thu, 28 Aug 2014 17:44:00 GMT

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!

Re: Cross table manipulation

Thu, 28 Aug 2014 18:36:20 GMT

Thank you very much!

Re: Cross table manipulation

jerem1234 — Thu, 28 Aug 2014 19:36:25 GMT

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

Thanks!!

Re: Cross table manipulation

MarcoWedel — Sat, 30 Aug 2014 18:15:47 GMT

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

Re: Cross table manipulation

Wed, 10 Sep 2014 10:57:10 GMT

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%

Re: Cross table manipulation

jerem1234 — Wed, 10 Sep 2014 17:30:18 GMT

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!

Re: Cross table manipulation

Wed, 10 Sep 2014 18:56:09 GMT

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

Re: Re: Cross table manipulation

jerem1234 — Wed, 10 Sep 2014 19:19:30 GMT

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!

Re: Re: Cross table manipulation

MarcoWedel — Wed, 10 Sep 2014 21:36:21 GMT

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

Re: Re: Re: Cross table manipulation

MarcoWedel — Wed, 10 Sep 2014 21:56:59 GMT

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

Re: Cross table manipulation

Thu, 11 Sep 2014 08:54:34 GMT

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%