Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
bobbydave
Creator III
Creator III

How to get around Year

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

GroupingMetricTarget
Otherxxxexpression detail
Servicesyyy
Projectszzz
Dataaaa


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.

0 Replies