Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

markus_hanssona
New Contributor II

Question about using crosstable

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.

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: Question about using crosstable

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 (%)]);

6 Replies
MVP
MVP

Re: Question about using crosstable

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 (%)]);

markus_hanssona
New Contributor II

Re: Question about using crosstable

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?

markus_hanssona
New Contributor II

Re: Question about using crosstable

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 (%)]);

MVP
MVP

Re: Question about using crosstable

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 (%)]);

MVP
MVP

Re: Question about using crosstable

Its the same thing... except you are storing the data in a qvd and then loading it... both of them should work....

markus_hanssona
New Contributor II

Re: Question about using crosstable

Thanks for the help.