Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 (%)")
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:
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.
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 (%)]);
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 (%)]);
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?
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 (%)]);
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 (%)]);
Its the same thing... except you are storing the data in a qvd and then loading it... both of them should work....
Thanks for the help.