topic Adding dates then values to null values in QlikView

Adding dates then values to null values

pauledrich — Mon, 17 Mar 2014 15:24:50 GMT

Hi and thanks for the help

I have read a couple of articles on this topic and also experimented within the load script but don't seem to be able to get it quite right.

The scenario:

I have a table 'MonthTargets' taken from a QVD with the fields Location, Month, Year,Product & MthTarget from which I have generated a TargetDate Field by 'MakeDate(Year,Month) AS TargetDate' which returns a date of 01/MM/YYYY - but I want to create weekly targets based on the monthly figure.

I need to create a date between the 1st of each month and End and also add the MthTarget to each of those date fields. I have tried using the previous function from a previous post which is :-

Loadcalcs:

Load *,

if(previous(Location)=Location,previous(TargetDate)) as PrevDate,

if(previous(Location)=Location,previous(MthTarget )) as PrevVal1;

Concatenate(MonthlyTargets)

Load Location,

TargetDate - IterNo() as TargetDate,

PrevVal1 as MthTarget

Resident MonthlyTargets

while TargetDate - IterNo() > PrevDate

order by Location, TargetDate desc;

drop field PrevDate, PrevVal1;

This solution on looks close.

And also attempted one using Peek as described in the technical brief by Henric - 'Generating Missing Data' but end up with 14m records being added.

I would really appreciate any detailed help on where I am going wrong.

Re: Adding dates then values to null values

pauledrich — Mon, 17 Mar 2014 16:00:40 GMT

Anyone able to help?

Re: Adding dates then values to null values

Mon, 17 Mar 2014 16:07:08 GMT

Hey Paul,

Would it be possible to attach a sample file with the tables you have and how you want the resultant table/results to look like. It will be helpful in addressing the issue.

Thanks

Re: Re: Adding dates then values to null values

pauledrich — Mon, 17 Mar 2014 16:23:43 GMT

Current					Desired
Location	Month	Year	Target	Date	Location	Month	Year	Target	Date
1	1	2014	1000	01/01/2014	1	1	2014	1000	01/01/2014
1	2	2014	1000	01/02/2014	1	1	2014	1000	02/01/2014
1	3	2014	1000	01/03/2014	1	1	2014	1000	03/01/2014
1	4	2014	1000	01/04/2014	1	1	2014	1000	04/01/2014
1	5	2014	1000	01/05/2014	1	1	2014	1000	05/01/2014
1	6	2014	1000	01/06/2014	1	1	2014	1000	06/01/2014
1	7	2014	1000	01/07/2014	1	1	2014	1000	07/01/2014
1	8	2014	1000	01/08/2014	1	1	2014	1000	08/01/2014
1	9	2014	1000	01/09/2014	1	1	2014	1000	09/01/2014
1	10	2014	1000	01/10/2014	1	1	2014	1000	10/01/2014
1	11	2014	1000	01/11/2014	1	1	2014	1000	11/01/2014
1	12	2014	1000	01/12/2014	1	1	2014	1000	12/01/2014
Continue through to year 31/12/2014

As you can see location 1 has a target on the 1st of each month - I would like to have the monthly target for each day of the month shown in a row for each date.

For this Dashboard I am using one master calendar linked to various fact tables.

Re: Re: Re: Adding dates then values to null values

Mon, 17 Mar 2014 16:41:18 GMT

Maybe like attached? Its a little cheesy solution but it might work for you from my understanding of your requirement.

Thanks

Re: Adding dates then values to null values

maxgro — Mon, 17 Mar 2014 18:50:18 GMT

try this

I changed your Target just to check the result

Let vMinDate = num(date('01/01/2014'));

Let vMaxDate = num(date('31/12/2014'));

Calendar:

LOAD Date, num(Month(Date)) as Month, Year(Date) as Year;

Load Date(recno()+$(vMinDate)-1) as Date

Autogenerate $(vMaxDate) - $(vMinDate) +1;

Table:

LOAD * INLINE [

Location, Month, Year, Target, DateSource

1, 1, 2014, 1001, 01/01/2014

1, 2, 2014, 1002, 01/02/2014

1, 3, 2014, 1003, 01/03/2014

1, 4, 2014, 1004, 01/04/2014

1, 5, 2014 ,1005, 01/05/2014

1, 6, 2014 ,1006, 01/06/2014

1, 7, 2014, 1007, 01/07/2014

1, 8, 2014, 1008, 01/08/2014

1, 9, 2014, 1009, 01/09/2014

1, 10, 2014, 1010, 01/10/2014

1, 11, 2014, 1011, 01/11/2014

1, 12, 2014, 1012, 01/12/2014

];

left join (Table) load Year, Month, Date Resident Calendar;

DROP Field Year, Month From Table;