Back filled data causing duplication

hartleyr — Sat, 16 Nov 2024 18:13:51 GMT

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;

Re: Back filled data causing duplication

hartleyr — Wed, 19 Aug 2020 11:11:05 GMT

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

topic Re: Back filled data causing duplication in QlikView

Back filled data causing duplication

Re: Back filled data causing duplication