Peek or Previous - Using last number

[cpiocpio]

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

[MK_QSL]

Hope this will help you

[MK_QSL]

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;

[swuehl]

Please read

Generating Missing Data In QlikView

[Gysbert_Wassenaar · Accepted solution]

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;

[Nicole-Smith]

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;

[cpiocpio]

Many thanks to all that responded, very good answers all round