How to get around Year

bobbydave — Wed, 25 Nov 2020 16:16:04 GMT

So I have a table and Metric is my key throughout all tables.

Grouping	Metric
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.

topic How to get around Year in QlikView

How to get around Year