Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Cross table manipulation

Hi

I want to manipulate the following table into a crosstable.

PRIMARY_TICKERSALES_BRAZILFY 2012SALES_BRAZILFY 2013SALES_BRAZILFY 2014
0486.HK65450
1010.SE401050

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_TICKERRegionYearData
0486.HKSALES_BRAZILFY 201265
0486.HKSALES_BRAZILFY 201345
0486.HKSALES_BRAZILFY 20140
1010.SESALES_BRAZILFY 201240
1010.SESALES_BRAZILFY 201310
1010.SESALES_BRAZILFY 201450
1 Solution

Accepted Solutions
jerem1234
Specialist II
Specialist II

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!

View solution in original post

11 Replies
jerem1234
Specialist II
Specialist II

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!

Not applicable
Author

Thank you very much!

jerem1234
Specialist II
Specialist II

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;

QlikCommunity_Thread_131168_Pic1.JPG.jpg

hope this helps

regards

Marco

Not applicable
Author

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

TickersGS EPS YTD revisionsGS EPS 12m revisions
FY14FY15FY16FY17FY14FY15FY16FY17
M.L2.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
Specialist II
Specialist II

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!

Not applicable
Author

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

jerem1234
Specialist II
Specialist II

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:

Import.PNG.png

Hope this helps!

MarcoWedel

Hi again,

let me try one possible solution:

QlikCommunity_Thread_131168_Pic1.JPG.jpg

QlikCommunity_Thread_131168_Pic2.JPG.jpg

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