So I have a table and Metric is my key throughout all tables.
| Grouping | Metric | Target |
|---|
| Other | xxx | | expression detail |
| Services | yyy | | |
| Projects | zzz | | |
| Data | aaa | | |
This loads data from 2014-2016.
Data from my Fact_Table and Metric table is made up of YYYYMM_nameOfFile.xlsx and the date is captured using
floor(makeDate(num(left(FileName(),4)), num(mid(FileName(), 5,2)), 1)) as %Date
Now Target is made up of YYYY_nameOfFile.xlsx and Metric is a key here also. If I am only loading one year (in which case, the Target in the file is static and would always be only one year like 2014 and is the same for the next year and so on.
Below you will see the third, fourth and fifth Target_Tables that have 3 different Years and the Target is going to change Year on Year.
I keep getting synthetic key errors when I load this. I need to separate so they are recognised as different years.
Year is in my Calendar table also.
At the moment, all data is loaded into target and for each metric, all 3 values (2014,2015 and 2016) for that metric are showing in one cell
Target_Table:
LOAD Metric,
Target
FROM
[$(vSource)\Control\*_Control_Target.xlsx]
(ooxml, embedded labels, table is Sheet1);
Target_Table:
LOAD Metric,
Target
FROM
[$(vSource)\Technology\*_Technology_Target.xlsx]
(ooxml, embedded labels, table is Sheet1);
******************************************************************************************
Target_Table:
LOAD Metric,
Target
//floor(num(left(FileName(),4))) as Year
FROM
[$(vSource)\Financial\2014_Financial_Target.xlsx]
(ooxml, embedded labels, table is Sheet1);
Target_Table:
LOAD Metric,
Target
//floor(num(left(FileName(),4))) as Year
FROM
[$(vSource)\Financial\2015_Financial_Target.xlsx]
(ooxml, embedded labels, table is Sheet1);
Target_Table:
LOAD Metric,
Target
//floor(num(left(FileName(),4))) as Year
FROM
[$(vSource)\Financial\2016_Financial_Target.xlsx]
(ooxml, embedded labels, table is Sheet1);
***************************************************************************
All help appreciated.
