topic Re: Peek or Previous - Using last number in QlikView

Peek or Previous - Using last number

cpiocpio — Tue, 11 Feb 2014 17:34:39 GMT

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

Re: Peek or Previous - Using last number

MK_QSL — Tue, 11 Feb 2014 17:54:27 GMT

Hope this will help you

Re: Peek or Previous - Using last number

MK_QSL — Tue, 11 Feb 2014 17:55:39 GMT

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;

Re: Peek or Previous - Using last number

swuehl — Tue, 11 Feb 2014 18:02:09 GMT

Please read

Generating Missing Data In QlikView

Re: Peek or Previous - Using last number

Gysbert_Wassenaar — Tue, 11 Feb 2014 18:04:01 GMT

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;

Re: Peek or Previous - Using last number

Nicole-Smith — Tue, 11 Feb 2014 18:07:24 GMT

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;

Re: Peek or Previous - Using last number

cpiocpio — Tue, 11 Feb 2014 23:55:24 GMT

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