Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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
Valued Contributor II

Re: Cross table manipulation

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!

11 Replies
jerem1234
Valued Contributor II

Re: Cross table manipulation

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

Re: Cross table manipulation

Thank you very much!

jerem1234
Valued Contributor II

Re: Cross table manipulation

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

Thanks!!

Re: Cross table manipulation

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

Re: Cross table manipulation

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
Valued Contributor II

Re: Cross table manipulation

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

Re: Cross table manipulation

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

jerem1234
Valued Contributor II

Re: Re: Cross table manipulation

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!

Re: Re: Cross table manipulation

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

Community Browser