Peek function issue

[scotly-victor]

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 ;

[sunny_talwar]

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 · Accepted solution]

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]

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]

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]

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

[#7]

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]

Yep, good spot