6 Replies Latest reply: Sep 29, 2017 2:29 AM by 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 (%)

 Company 2016 2015 2014 2013 Volkswagen AG 12,84% 7,31% 14,56% 13,36%

Operating Margin (%)

 Company Year Operating Margin (%) Volkswagen AG 2016 3,27% Volkswagen AG 2015 -1,91% Volkswagen AG 2014 6,27% Volkswagen AG 2013 5,92%

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

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

Company,

"2016",

"2015",

"2014",

"2013"

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

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

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:

 Year Company Sum([EBITDA Margin (%)]) Sum([Operating Margin (%)]) 2016 Volkswagen AG 0,1283950163 0,00% 2015 Volkswagen AG 0,07307822141 0,00% 2014 Volkswagen AG 0,145649962 0,00% 2013 Volkswagen AG 0,1336043897 0,00% 2016 Volkswagen AG 0 3,27% 2015 Volkswagen AG 0 -1,91% 2014 Volkswagen AG 0 6,27% 2013 Volkswagen AG 0 5,92% - - 0 0,00%

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

 Year Company Sum([EBITDA Margin (%)]) Sum([Operating Margin (%)]) 2016 Volkswagen AG 0,1283950163 3,27% 2015 Volkswagen AG 0,07307822141 -1,91% 2014 Volkswagen AG 0,145649962 6,27% 2013 Volkswagen AG 0,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

Try this

Table:

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

Company,

"2016",

"2015",

"2014",

"2013"

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

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

Left Join (Table)

Company,

"Year",

"Operating Margin (%)"

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

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

• ###### Re: Question about using crosstable

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

 Year Company Sum([EBITDA Margin (%)]) Sum([Operating Margin (%)]) 2016 Volkswagen AG 0,1283950163 0,00% 2015 Volkswagen AG 0,07307822141 0,00% 2014 Volkswagen AG 0,145649962 0,00% 2013 Volkswagen AG 0,1336043897 0,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

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

TmpTable:

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

Company,

"2016",

"2015",

"2014",

"2013"

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

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

Table:

NoConcatenate

Num#(Year) as Year,

"EBITDA Margin (%)"

Resident TmpTable;

DROP Table TmpTable;

Left Join (Table)

Company,

"Year",

"Operating Margin (%)"

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

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

• ###### Re: Question about using crosstable

This is how I did it:

[tempData]:

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

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]:

Company,

Num(Num#(Year)) as Year,

"EBITDA Margin (%)"

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

(qvd);

Company,

"Year",

"Operating Margin (%)"

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

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

• ###### 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....

• ###### Re: Question about using crosstable

Thanks for the help.