topic Re: Peek vs Previous - Use Last Number PLUS!! in QlikView

Peek vs Previous - Use Last Number PLUS!!

cpiocpio — Wed, 12 Feb 2014 16:23:19 GMT

Hi all

I am back.

After an excellent response yesterday, I thought thats one in the bag, move on, but no.

The requirement has been extended.

Still generating missing dates, and repeating previous values but PER machine

So in the attched, i have used INLINE and out in only a few rows, in 3 coloumns

LOAD * INLINE [
    DataDate, MachineName, Mileage
    02/06/2009,SW81AA,638
    04/06/2009,SW88AA,1600
    05/06/2009,SW88AA,1600
    07/06/2009,33KM85,309
];

The required OUTPUT is, that it should repeat per Machine Name per day :

02/06/2009;"SW81AA";638

03/06/2009;"SW81AA";638

04/06/2009;"SW81AA";638

04/06/2009;"SW88AA";1600

05/06/2009;"SW88AA";1600

05/06/2009;"SW81AA";638

06/06/2009;"SW88AA";1600

06/06/2009;"SW81AA";638

07/06/2009;"33KM85";309

07/06/2009;"SW88AA";1600

07/06/2009;"SW81AA";638

Many thanks

Re: Peek vs Previous - Use Last Number PLUS!!

marcus_sommer — Wed, 12 Feb 2014 16:30:01 GMT

See here:

http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/08/peek-vs-previous-when-to-use-each

- Marcus

Re: Peek vs Previous - Use Last Number PLUS!!

Nicole-Smith — Wed, 12 Feb 2014 16:54:44 GMT

I think this should do it:

Result:

LOAD * INLINE [

    DataDate, MachineName, Mileage

    02/06/2009,SW81AA,638

    04/06/2009,SW88AA,1600

    05/06/2009,SW88AA,1600

    07/06/2009,33KM85,309

];

Temp2:

load DataDate, MachineName, previous(DataDate) as NextDate, Mileage

Resident Result

order by DataDate desc;

MaxDate:

Load max(DataDate) as MaxDate

Resident Temp2;

LET vMaxDate = PEEK('MaxDate', 0, 'MaxDate');

DROP TABLE MaxDate;

Concatenate(Result)

Load Date(DataDate + IterNo()) as DataDate, MachineName, Mileage

Resident Temp2

while DataDate + IterNo() <= $(vMaxDate);

drop Table Temp2;

Re: Re: Peek vs Previous - Use Last Number PLUS!!

rwunderlich — Wed, 12 Feb 2014 17:29:03 GMT

Or use Qlikview Components - A script library for Qlikview development

Result:

LOAD * INLINE [

DataDate, MachineName, Mileage

02/06/2009,SW81AA,638

04/06/2009,SW88AA,1600

05/06/2009,SW88AA,1600

07/06/2009,33KM85,309

];

CALL Qvc.GetFieldValues('vStats', 'DataDate');

LET Qvc.ExpandInterval.v.RangeCeiling = vStats.Max;

CALL Qvc.ExpandInterval('Result', 'DataDate', '', 'MachineName');

CALL Qvc.Cleanup

-Rob

http://masterssummit.com

Re: Peek vs Previous - Use Last Number PLUS!!

cpiocpio — Thu, 13 Feb 2014 10:05:27 GMT

Hi Nicole

Good but not quite there, When a new value is supplied for an equipment type, then that is the value that should carry on, not OLD and new

It works up to a point until the
next reading is available then repeats new reading and old per day.

So you see when it gets to 21st, it starts repeating that days value but also repeats the earlier one

Input:

03/01/2014	02SE45	981
09/01/2014	02SE45	981
21/01/2014	02SE45	982
22/01/2014	02SE45	982

QV output:

03/01/2014	02SE45	981
04/01/2014	02SE45	981
05/01/2014	02SE45	981
06/01/2014	02SE45	981
07/01/2014	02SE45	981
08/01/2014	02SE45	981
09/01/2014	02SE45	981
10/01/2014	02SE45	981
11/01/2014	02SE45	981
12/01/2014	02SE45	981
13/01/2014	02SE45	981
14/01/2014	02SE45	981
15/01/2014	02SE45	981
16/01/2014	02SE45	981
17/01/2014	02SE45	981
18/01/2014	02SE45	981
19/01/2014	02SE45	981
20/01/2014	02SE45	981
21/01/2014	02SE45	981
21/01/2014	02SE45	982
22/01/2014	02SE45	981
22/01/2014	02SE45	982
23/01/2014	02SE45	981
23/01/2014	02SE45	982
24/01/2014	02SE45	981
24/01/2014	02SE45	982
25/01/2014	02SE45	981
25/01/2014	02SE45	982
26/01/2014	02SE45	981
26/01/2014	02SE45	982
27/01/2014	02SE45	981
27/01/2014	02SE45	982
28/01/2014	02SE45	981
28/01/2014	02SE45	982
29/01/2014	02SE45	981
29/01/2014	02SE45	982
30/01/2014	02SE45	981
30/01/2014	02SE45	982
31/01/2014	02SE45	981
31/01/2014	02SE45	982
01/02/2014	02SE45	981
01/02/2014	02SE45	982
02/02/2014	02SE45	981
02/02/2014	02SE45	982
03/02/2014	02SE45	981
03/02/2014	02SE45	982
04/02/2014	02SE45	981
04/02/2014	02SE45	982
05/02/2014	02SE45	981
05/02/2014	02SE45	982
06/02/2014	02SE45	981
06/02/2014	02SE45	982
07/02/2014	02SE45	981
07/02/2014	02SE45	982
08/02/2014	02SE45	981
08/02/2014	02SE45	982
09/02/2014	02SE45	981
09/02/2014	02SE45	982
10/02/2014	02SE45	981
10/02/2014	02SE45	982
11/02/2014	02SE45	981
11/02/2014	02SE45	982

Re: Peek vs Previous - Use Last Number PLUS!!

Thu, 13 Feb 2014 10:09:29 GMT

As an exercice, I did the job yesterday but did not send you (there were many answers already)

It may help you

There is also a very interesting post by HIC about generating missing data.

Fabrice

Re: Peek vs Previous - Use Last Number PLUS!!

cpiocpio — Thu, 13 Feb 2014 10:32:35 GMT

Hi Aunez

Many thanks for this, it is looking promising.

Nice to have some comments in the script, to "guide us newbies"

Just pointed at some real data, and thats looks good to me to.

Now passed over to end user to check, will advise

Many thanks again

Mike

Re: Peek vs Previous - Use Last Number PLUS!!

Thu, 13 Feb 2014 10:35:28 GMT

I had modified some your data to make more tests

But you will enter real data when tests are ok

Fabrice

Re: Re: Peek vs Previous - Use Last Number PLUS!!

Nicole-Smith — Thu, 13 Feb 2014 13:20:56 GMT

Looks like you already got your answer, but here is my fixed solution just in case:

Result:

LOAD * INLINE [

    DataDate, MachineName, Mileage

    02/06/2009,SW81AA,638

    04/06/2009,SW88AA,1600

    06/06/2009,SW88AA,1700

    07/06/2009,33KM85,309

];

Temp2:

load DataDate, MachineName, if(MachineName=previous(MachineName),previous(DataDate)) as NextDate, Mileage

Resident Result

order by MachineName, DataDate desc;

MaxDate:

Load max(DataDate) as MaxDate

Resident Temp2;

LET vMaxDate = PEEK('MaxDate', 0, 'MaxDate');

DROP TABLE MaxDate;

Concatenate(Result)

Load Date(DataDate + IterNo()) as DataDate, MachineName, Mileage

Resident Temp2

while if(len(NextDate)>0, DataDate + IterNo() < NextDate, DataDate + IterNo() <= $(vMaxDate));

drop Table Temp2;

Re: Peek vs Previous - Use Last Number PLUS!!

Fri, 14 Feb 2014 08:57:59 GMT

Mike,

Is it OK ? closed ?

Fabrice

Re: Peek vs Previous - Use Last Number PLUS!!

cpiocpio — Mon, 17 Feb 2014 13:44:00 GMT

Once again, I say many thanks to all .

I had to nominate One answer as correct, but all had their merits. I chose Nicole Smiths as CORRECT, since nearest to desired results and did not involve any add-on scripts etc. ALL were great and very useful thought.

Re: Peek vs Previous - Use Last Number PLUS!!

cpiocpio — Mon, 17 Feb 2014 13:44:48 GMT

Yes, thatnk you just posted a reply - been out and about