Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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