6 Replies Latest reply: Sep 29, 2017 2:29 AM by Markus Hansson RSS

    Question about using crosstable

    Markus Hansson

      Hello, I am trying to understand how I can read some reports correctly into Qlik sense, the problem that I am having is that some reports use Year as a column, and other reports have 2016, 2015, 2014 etc as columns.

       

      So for example I am ending up with reports that looks like this (this is just an example):

       

      EBITDA Margin (%)

      Company2016201520142013
      Volkswagen AG12,84%7,31%14,56%

      13,36%

        

      Operating Margin (%)

      CompanyYearOperating Margin (%)
      Volkswagen AG20163,27%
      Volkswagen AG2015-1,91%
      Volkswagen AG20146,27%
      Volkswagen AG2013

      5,92%

       

      My first thought was to simply read EBITDA Margin (%) as a crosstable using the following formula:

       

      Crosstable(Year, "EBITDA Margin (%)")

      LOAD

          Company,

          "2016",

          "2015",

          "2014",

          "2013"

      FROM [lib://AttachedFiles/data.xlsx]

      (ooxml, embedded labels, table is [EBITDA Margin (%)]);

       

      LOAD

          Company,

          "Year",

          "Operating Margin (%)"

      FROM [lib://AttachedFiles/data.xlsx]

      (ooxml, embedded labels, table is [Operating Margin (%)]);

       

      However, doing this will make qlik sense read the data like this:

       

          

      YearCompanySum([EBITDA Margin (%)])Sum([Operating Margin (%)])
      2016Volkswagen AG0,12839501630,00%
      2015Volkswagen AG0,073078221410,00%
      2014Volkswagen AG0,1456499620,00%
      2013Volkswagen AG0,13360438970,00%
      2016Volkswagen AG03,27%
      2015Volkswagen AG0-1,91%
      2014Volkswagen AG06,27%
      2013Volkswagen AG05,92%
      --0

      0,00%

       

      What I would like Qlik Sense to do is to read the data like this:

       

          

      YearCompanySum([EBITDA Margin (%)])Sum([Operating Margin (%)])
      2016Volkswagen AG0,12839501633,27%
      2015Volkswagen AG0,07307822141-1,91%
      2014Volkswagen AG0,1456499626,27%
      2013Volkswagen AG0,1336043897

      5,92%

       

      How do I load two tables like this properly? If table EBITDA Margin (%) was sorted the same way as Operating Margin (%) this would work fine, but because I am using crosstable qlik sense reads the the data differently.

        • Re: Question about using crosstable
          Sunny Talwar

          Try this

           

          Table:

          Crosstable(Year, "EBITDA Margin (%)")

          LOAD

              Company,

              "2016",

              "2015",

              "2014",

              "2013"

          FROM [lib://AttachedFiles/data.xlsx]

          (ooxml, embedded labels, table is [EBITDA Margin (%)]);

           

          Left Join (Table)

          LOAD

              Company,

              "Year",

              "Operating Margin (%)"

          FROM [lib://AttachedFiles/data.xlsx]

          (ooxml, embedded labels, table is [Operating Margin (%)]);

          • Re: Question about using crosstable
            Markus Hansson

            If I use left join I will end up with the following:

                

            YearCompanySum([EBITDA Margin (%)])Sum([Operating Margin (%)])
            2016Volkswagen AG0,12839501630,00%
            2015Volkswagen AG0,073078221410,00%
            2014Volkswagen AG0,1456499620,00%
            2013Volkswagen AG0,13360438970,00%

             

            I think I found the problem though, it is because Crosstable stores Year as a literal of text, and not numbers. Crosstable numeric values

             

            So the question is what will be the correct formula in my example? How do I convert Year in this case to numeric values?

              • Re: Question about using crosstable
                Sunny Talwar

                You are right... totally forgot about that... try this

                 

                TmpTable:

                Crosstable(Year, "EBITDA Margin (%)")

                LOAD

                    Company,

                    "2016",

                    "2015",

                    "2014",

                    "2013"

                FROM [lib://AttachedFiles/data.xlsx]

                (ooxml, embedded labels, table is [EBITDA Margin (%)]);


                Table:

                NoConcatenate

                LOAD Company,

                    Num#(Year) as Year,

                    "EBITDA Margin (%)"

                Resident TmpTable;


                DROP Table TmpTable;

                 

                Left Join (Table)

                LOAD

                    Company,

                    "Year",

                    "Operating Margin (%)"

                FROM [lib://AttachedFiles/data.xlsx]

                (ooxml, embedded labels, table is [Operating Margin (%)]);

              • Re: Question about using crosstable
                Markus Hansson

                This is how I did it:

                 

                [tempData]:

                Crosstable(Year, "EBITDA Margin (%)")

                LOAD

                    Company,

                    "2016",

                    "2015",

                    "2014",

                    "2013"

                FROM [lib://AttachedFiles/data.xlsx]

                (ooxml, embedded labels, table is [EBITDA Margin (%)]);

                 

                Store tempData INTO [lib://data/tempdata.qvd] ;

                 

                drop TABLE tempData;

                 

                Data]:

                LOAD

                    Company,

                    Num(Num#(Year)) as Year,

                    "EBITDA Margin (%)"

                FROM [lib://data/tempdata.qvd]

                (qvd);

                 

                LOAD

                    Company,

                     "Year",

                     "Operating Margin (%)"

                FROM [lib://AttachedFiles/data.xlsx]

                (ooxml, embedded labels, table is [Operating Margin (%)]);