Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
hartleyr
Contributor III
Contributor III

Back filled data causing duplication

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_0-1597315716598.png

 

 

Labels (4)
1 Solution

Accepted Solutions
hartleyr
Contributor III
Contributor III
Author

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

View solution in original post

1 Reply
hartleyr
Contributor III
Contributor III
Author

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