Get Data for rest dates which are not there in dataset

[diptojyotidutta]

Hi everyone,

I have 2 fields Date and Sales,

I have 3 rows. They are as stated under:-

Date,Sales

12/01/2012,45

14/01/2012,12

17/01/2012,47

Now their is no data of sales for 13, 15 and 16 of jan 2012. I want that the dates which are not there in the datasets show the previous value like 13/01/2012 will have sales value 45 because 12/01/2012 has it. 15/01/2012 and 16/01/2012 will have sales value 12 because 14/01/2012 has it. What must be the syntax?

[Arthur_Fong · Accepted solution]

Try this:

Script:

Raw:
load * inline [
Date,Sales
12/01/2012,45
14/01/2012,12
17/01/2012,47
];

NoConcatenate
Data:
load Date#(Date,'DD/MM/YYYY')AS Date,Sales
resident Raw;

drop table Raw;

Date:
load min(Date) as minDate,
	 max(Date)as maxDate
resident Data;

let vMin =peek('minDate',0,'Date');
let vMax =peek('maxDate',0,'Date');
drop table Date;

concatenate(Data)
Data2:
load Date($(vMin)-1+IterNo(),'DD/MM/YYYY') AS Date,0 as Sales
AutoGenerate 1
while $(vMin)-1+IterNo()<$(vMax)+1;

NoConcatenate
DataSum:
load Date,Sum(Sales)as Sales
resident Data
group by Date
order by Date;

drop table Data;

DataLogic:
load Date,if(RowNo()=1,Sales,if(Sales=0,peek(SalesFull),Sales)) as SalesFull
resident DataSum;
drop table DataSum;

exit script;

Thanks and regards,

Arthur Fong 

[diptojyotidutta]

Thanks it worked