11 Replies Latest reply: Sep 11, 2014 4:54 AM by Roderick McKeown RSS

    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
        • Re: Cross table manipulation
          Jeremiah Kurpat

          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

              Thank you very much!

              • 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%
                  • Re: Cross table manipulation
                    Jeremiah Kurpat

                    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

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

                          • Re: Re: Cross table manipulation
                            Jeremiah Kurpat

                            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
                          Marco Wedel

                          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

                            • Re: Re: Re: Cross table manipulation
                              Marco Wedel

                              or maybe better:

                               

                              QlikCommunity_Thread_131168_Pic3.JPG.jpg

                               

                              QlikCommunity_Thread_131168_Pic4.JPG.jpg

                               

                               

                              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

                                  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

                                   

                                  30313233343536373839404142434445
                                  TickersCompany nameSedolSectorRatingListMarket cap (current)PriceTPUpsideTPPGS EPS YTD revisionsGS EPS 12m revisionsGS EPS 1w revisionsConsensus
                                    EPS YTD revisions
                                  Consensus EPS 12m
                                    revisions
                                  FY14FY15FY16FY17FY14FY15FY16FY17FY14FY15FY16FY17FY14FY15FY14FY15
                                  MAERSKb.COA.P. Moeller-Maersk4253048TransNeutral                           
                                    42,382
                                  14370.0014500.001%12 months 0.0%0.0%0.0%0.0%5.5%-3.8%14.1%2.7%
                                  A2.MIA2a SPA5499131UtilitiesNeutral 2,596 0.830.9312%12 months11.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.ASAalbertsB1W8P14SmallMidBuy                             
                                    2,298
                                  21.0129.0038%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.VXABB Ltd7108899MachinerySell                           
                                    39,657
                                  20.8520.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%
                          • Re: Cross table manipulation
                            Marco Wedel

                            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