Back filled data causing duplication

[hartleyr]

Hi All

I'm hoping for some help with back filling data.  I have managed to get 99% of the way there, but I'm having an issue with duplication occurring which means the incorrect data is associated

I am pulling in data for our Electricity RAG banding.  Banding/Pricing is reviewed yearly, and for all but one of our areas it hasn't changed since 01/04/2018.  For the one that has changed, we have changes yearly - 01/04/18, 01/04/19 & 01/04/2020.

For those that haven't changed I get a full set of associated data from my script.  For the one that changes however, I have the correct associations to the 'set date' but it also associates the same dates to the 01/04/18 'set date' and I cant understand why - I'm assuming it'll be something simple in the script

Any pointers would be greatly appreciated

The script I am using is as follows;

Price_Map:
Mapping load
Date(Floor(Date),'DD/MM/YYYY')&'_'&[MPAN Prefix]&'_'&RAG as %PriceKey,
[Price per kwh]
From $(vOutputPath)Electricity RAG Rates.qvd (qvd);

Pricing:
load
Date(Floor(Date),'DD/MM/YYYY')&'_1' as SetDate,
[MPAN Prefix],
interval(num#(Time,'0.00000000'),'hh:mm') as Time,
RAG,
applymap('Price_Map',Date(Floor(Date),'DD/MM/YYYY')&'_'&[MPAN Prefix]&'_'&RAG) as [Price per kWh]
from $(vOutputPath)Electricity RAGs WD.qvd (qvd);

concatenate

load

Date(Floor(Date),'DD/MM/YYYY')&'_0' as SetDate,
[MPAN Prefix],
interval(num#(Time,'0.00000000'),'hh:mm') as Time,
RAG,
applymap('Price_Map',Date(Floor(Date),'DD/MM/YYYY')&'_'&[MPAN Prefix]&'_'&RAG) as [Price per kWh]
from $(vOutputPath)Electricity RAGs WE.qvd (qvd);

Store * from Pricing into $(vOutputDest)Electricity RAG Pricing.qvd (qvd);
//Drop table Pricing;

for each vPrefix in 10,11,12,13,14,15,16,17,18,19,20,21,22,23

Transform:
load
distinct(Date(Floor(Date),'DD/MM/YYYY')) as IndexDate,
Date(Floor(Date),'DD/MM/YYYY') as SetDate

from $(vOutputPath)Electricity RAGs WD.qvd (qvd)
where [MPAN Prefix] = $(vPrefix);

if NoOfRows('Transform') >0 then

concatenate


MinMaxDate:
Load Min (IndexDate) as MinDate
,Max(IndexDate) as MaxDate

resident Transform
where not(isnull(IndexDate));


Let vMinDate = Peek('MinDate',-1,'MinMaxDate') - 1;
Let vMaxDate = if(Peek('MaxDate',-1,'MinMaxDate')>=IndexDate, Peek('MaxDate',-1,'MinMaxDate'),Today());


Join (Transform)

Load Date(recno()+$(vMinDate)) as IndexDate Autogenerate vMaxDate - vMinDate;





FillResults:

concatenate Load

if(isnull(IndexDate),peek(IndexDate),IndexDate) as IndexDate,
if(isnull(SetDate),peek(SetDate),SetDate) as SetDate,
if(isnull(weekday(IndexDate)),peek(weekday(IndexDate)),Weekday(IndexDate)) as day,
if(isnull(Networkdays(IndexDate,IndexDate)),peek(Networkdays(IndexDate,IndexDate)),Networkdays(IndexDate,IndexDate)) as daytype

Resident Transform
Order By IndexDate;

drop fields MinDate, MaxDate;
Store * From Transform into $(vOutputDest)Electricity Set Dates.qvd (qvd);

drop table Transform;
elseif NoOfRows('Transform') =0; drop table 'Transform';
end if;

load IndexDate,
SetDate&'_'&daytype as SetDate
,day
from $(vOutputDest)Electricity Set Dates.qvd (qvd)
where not(isnull(daytype))
;
next;

[hartleyr · Accepted solution]

Duplication was caused due to the connecting key being too generic.  Adding in a further identifier into this field has removed the duplication and corrected the issue