Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
scotly-victor
Not applicable

Peek function issue

Hi Every one ,

My Input date

id,date

1,1/2/2016

1,6/2/2016

1,7/2/2016

1,11/2/2016

1,13/2/2016

1,16/2/2016

1,21/2/2016

];

Desired Output:

id,Startdate,Enddate

1,1/2/2016,6/2/2016

1,6/2/2016,7/2/2016

1,7/2/2016,11/2/2016

1,11/2/2016,13/2/2016

1,13/2/2016,16/2/2016

1,16/2/2016,21/2/2016

1,21/2/2016,4/11/2016

];

My Script:

load

date as Startdate,

id,

if(peek(id,1)=id,peek(date,1),today()) as Enddate

RESIDENT Date1 order by id ,date ;

1 Solution

Accepted Solutions
vinieme12
Not applicable

Re: Peek function issue

Try as below

TEMP:
LOAD * INLINE [
id,date
1,1/2/2016
1,6/2/2016
1,7/2/2016
1,11/2/2016
1,13/2/2016
1,16/2/2016
1,21/2/2016
]
;


FINAL:

LOAD *
,
if(id=Previous(id),Previous(date),date(today(),'DD/MM/YYYY')) as EndDate

RESIDENT TEMP
Order BY id,date DESC;

DROP TABLE TEMP;

7 Replies
sunny_talwar
Not applicable

Re: Peek function issue

May be like this

LOAD id,

          date as Startdate,

          Date(If(Peek('id') = id, Peek('date'), Today())) as Enddate

Resident Date1

Order By id, date DESC;

vinieme12
Not applicable

Re: Peek function issue

Try as below

TEMP:
LOAD * INLINE [
id,date
1,1/2/2016
1,6/2/2016
1,7/2/2016
1,11/2/2016
1,13/2/2016
1,16/2/2016
1,21/2/2016
]
;


FINAL:

LOAD *
,
if(id=Previous(id),Previous(date),date(today(),'DD/MM/YYYY')) as EndDate

RESIDENT TEMP
Order BY id,date DESC;

DROP TABLE TEMP;

sunny_talwar
Not applicable

Re: Peek function issue

Some modifications:

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='D/M/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Date1:

LOAD * Inline [

id,date

1,1/2/2016

1,6/2/2016

1,7/2/2016

1,11/2/2016

1,13/2/2016

1,16/2/2016

1,21/2/2016

];

FinalDate:

LOAD id,

  date as Startdate,

    Date(If(Peek('id') = id, Previous(date), Today())) as Enddate

Resident Date1

Order By id, date DESC;

DROP Table Date1;

antoniotiman
Not applicable

Re: Peek function issue

Try

NoConcatenate
load
date as Startdate,
id,
Date(if(peek(id)=id,previous(date),today())) as Enddate
RESIDENT Date1 order by id ,date desc;
Drop Table
Date1;

sunny_talwar
Not applicable

Re: Peek function issue

vinieme12‌ -

I saw you added the If statement to check for id. I would also suggest to add id to order by statement ahead of date to make sure the ordering take place first by id and then by date

Not applicable

Re: Peek function issue

Hi Scotly,

Just try this way,

T1:

LOAD * INLINE [
id,date

1,10

1,20

1,30

1,40

1,50

1,60

1,70

]
;

t2:
Load date as Date1,Previous(date) as date2
Resident T1 Order by date Desc;

Regards,

Ravi Kancharla

vinieme12
Not applicable

Re: Peek function issue

Yep, good spot