Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
A table will display blanks when there is no value for any particular day
What is required is previous value to repeat until there is a new value in the data
Core data is
01/01/2012 | 23 |
02/01/2012 | 55 |
04/01/2012 | 33 |
06/01/2012 | 16 |
08/01/2012 | 12 |
We pad out the dates, ie insert all dates in the timeperiod, not just those in the data
What is required is this, you can see below, missing dates are inserted (this does happen) and then where there was no Value for a given date e.g. 03/01/2012, then the previous value is used, there may be days when the same value has to get repeated several times
and it is the repeat that is not happening
01/01/2012 | 23 |
02/01/2012 | 55 |
03/01/2012 | 55 |
04/01/2012 | 33 |
05/01/2012 | 33 |
06/01/2012 | 16 |
07/01/2012 | 16 |
08/01/2012 | 12 |
Result:
LOAD DataDate, Data
FROM [DataSource.xlsx] (ooxml, embedded labels, table is Sheet1);
Temp2:
load DataDate, previous(DataDate) as NextDate, Data
Resident Result
order by DataDate desc;
Concatenate(Result)
Load DataDate + IterNo() as DataDate, Data
Resident Temp2
while DataDate + IterNo() < NextDate;
drop Table Temp2;
Hope this will help you
Test:
Load * Inline
[
Date, Value
01/01/2012, 23
02/01/2012, 55
04/01/2012, 33
06/01/2012, 16
08/01/2012, 12
];
MinMaxDate:
Load
Min(Date) as MinDate,
Max(Date) as MaxDate
Resident Test;
Let vMinDate = NUM(Peek('MinDate',0,'MinMaxDate'));
Let vMaxDate = NUM(Peek('MaxDate',0,'MinMaxDate'));
Drop Table MinMaxDate;
Join (Test)
Load
Date($(vMinDate) + RowNo() - 1) as Date
AutoGenerate $(vMaxDate) - $(vMinDate)+1;
FinalTable:
NoConcatenate
Load Date,
If(IsNull(Value), Peek(Value), Value) as Value
Resident Test
Order By Date;
Drop Table Test;
Please read
Result:
LOAD DataDate, Data
FROM [DataSource.xlsx] (ooxml, embedded labels, table is Sheet1);
Temp2:
load DataDate, previous(DataDate) as NextDate, Data
Resident Result
order by DataDate desc;
Concatenate(Result)
Load DataDate + IterNo() as DataDate, Data
Resident Temp2
while DataDate + IterNo() < NextDate;
drop Table Temp2;
Another approach (in this one I also calculate DayNum instead of you having to pull it in from that other Excel file):
MinAndMaxDates:
LOAD min(DataDate) as MinDate,
max(DataDate) as MaxDate
FROM DataSource.xlsx
(ooxml, embedded labels, table is Sheet1);
let MinDate = num(Peek('MinDate',0,'MinAndMaxDates'));
let MaxDate = num(Peek('MaxDate',0,'MinAndMaxDates'));
let StartDayNum = num(MakeDate(2011,6,1))-1;
DROP TABLE MinAndMaxDates;
Data:
LOAD *, DataDate-$(StartDayNum) as DayNum;
Load Date($(MinDate)-1+iterno()) as DataDate autogenerate 1 While iterno() <= $(MaxDate)+1 - $(MinDate) ;
let MinDate = null();
let MaxDate = null();
LEFT JOIN (Data)
LOAD DataDate,
Data
FROM DataSource.xlsx
(ooxml, embedded labels, table is Sheet1);
DataFinal:
LOAD DataDate,
if(IsNull(Data),Peek(Data),Data) as Data
RESIDENT Data;
DROP TABLE Data;
Many thanks to all that responded, very good answers all round